SOLVED

# Chart of expenses over time

Copper Contributor

# Chart of expenses over time

Hi,

I have a table that displays spending over time - we have the expense and the start and end date of the period. The periods are not uniform.

Please find below an example.

 Expense Start date End date Amount Renovation of flat 01-02-18 10-03-18 €   1,000.00 Monitoring 01-01-18 16-03-18 €      600.00 Other costs 01-12-17 28-02-18 €      350.00

I need a way to visualize expenses over time - let's say a chart or another neat way.

I'm looking to do it without having to break down the periods in identical ones (let's say all quarters) - there are hundreds of lines.

Can this be achieved?

Thanks,

Kaloyan

6 Replies

# Re: Chart of expenses over time

Hi Kaloyan,

How your expenses are applied to the period? For example, Other costs starts in 4Q17 and ends in 1Q18. Shall expenses be distribute proportionally within period, or applied by the end of the period or what?

# Re: Chart of expenses over time

Hi Sergei,
Distributed proportionally, please

# Re: Chart of expenses over time

Proportionally to calendar days?

# Re: Chart of expenses over time

Hi,

This is going to be analyzed on quarterly and annual basis. I'm perfectly happy with just a quarterly breakdown though.
best response confirmed by Kaloyan Stoyanov (Copper Contributor)
Solution

# Re: Chart of expenses over time

Hi Kaloyan,

To make such analysis in any case you shall add the timeline over all periods (in months, quarters, years, whatever), calculate your total expenses on each such calendar period and build a summary on it as a chart and/or pivot table.

Examples how to do that with formulas are here

https://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/

https://www.myonlinetraininghub.com/excel-formula-to-spread-income-or-costs-over-months

However, if you have Excel 2010 or above easier to do that with Power Query (Get&Transform in 2016). Here are the steps (I assume you need total expenses, not by category).

Let convert your expenses range into the table (stay on any cell within and Ctrl+T) and name it Expenses.

After that query it (Data->From Table/Range) and generate few steps

- add new column which calculate daily expenses based on Amount and number of days between Start and End;

- create new column with list of dates between Start and End and expand it into the rows;

- remove all columns except Dates and Daily Expenses;

- proper field types are to assigned

Resulting script looks like

```let
Source = Excel.CurrentWorkbook(){[Name="Expenses"]}[Content],
AssignFieldsType = Table.TransformColumnTypes(Source,{
{"Expense", type text},
{"Start date", type date},
{"End date", type date},
{"Amount", Currency.Type} }),
ExpensePerCalendarDate = Table.AddColumn(AssignFieldsType, "Daily Expense",
each [Amount]/(Duration.Days([End date]-[Start date])+1)),
DatesFromStartToEnd = Table.AddColumn(ExpensePerCalendarDate, "Date",
each List.Dates([Start date], Duration.Days([End date]-[Start date])+1, Duration.From(1))),
RemoveUnusedColumns = Table.SelectColumns(DatesFromStartToEnd,{"Date", "Daily Expense"}),
ExpandDates = Table.ExpandListColumn(RemoveUnusedColumns, "Date"),
ChangeFiledsType = Table.TransformColumnTypes(ExpandDates,{
{"Date", type date},
{"Daily Expense", type number}})
in
ChangeFiledsType```

Close and load the table into data model with connection only

Not to create time groups manually be sure in Excel File->Options->Data automating grouping is not disabled

Stay on empty cell and in ribbon Insert->PivotChart, add Dates to axis and Daily Expenses to values.

Now to do some cosmetic with generated chart, result will be like this

You may drill up/down calendar periods using +/- buttons at the bottom/right.

And in attached file.

# Re: Chart of expenses over time

If I want to do something similar, but instead of amortizing throughout the period, I want it to keep adding. So if for example, for the renovation of flat between 2001-02-18 and 2010-03-18 it's \$1000,  I want every date in that period to show \$1000 plus the other expenses over that period.

Thank you,

Jose

1 best response

Accepted Solutions
best response confirmed by Kaloyan Stoyanov (Copper Contributor)
Solution

# Re: Chart of expenses over time

Hi Kaloyan,

To make such analysis in any case you shall add the timeline over all periods (in months, quarters, years, whatever), calculate your total expenses on each such calendar period and build a summary on it as a chart and/or pivot table.

Examples how to do that with formulas are here

https://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/

https://www.myonlinetraininghub.com/excel-formula-to-spread-income-or-costs-over-months

However, if you have Excel 2010 or above easier to do that with Power Query (Get&Transform in 2016). Here are the steps (I assume you need total expenses, not by category).

Let convert your expenses range into the table (stay on any cell within and Ctrl+T) and name it Expenses.

After that query it (Data->From Table/Range) and generate few steps

- add new column which calculate daily expenses based on Amount and number of days between Start and End;

- create new column with list of dates between Start and End and expand it into the rows;

- remove all columns except Dates and Daily Expenses;

- proper field types are to assigned

Resulting script looks like

```let
Source = Excel.CurrentWorkbook(){[Name="Expenses"]}[Content],
AssignFieldsType = Table.TransformColumnTypes(Source,{
{"Expense", type text},
{"Start date", type date},
{"End date", type date},
{"Amount", Currency.Type} }),
ExpensePerCalendarDate = Table.AddColumn(AssignFieldsType, "Daily Expense",
each [Amount]/(Duration.Days([End date]-[Start date])+1)),
DatesFromStartToEnd = Table.AddColumn(ExpensePerCalendarDate, "Date",
each List.Dates([Start date], Duration.Days([End date]-[Start date])+1, Duration.From(1))),
RemoveUnusedColumns = Table.SelectColumns(DatesFromStartToEnd,{"Date", "Daily Expense"}),
ExpandDates = Table.ExpandListColumn(RemoveUnusedColumns, "Date"),
ChangeFiledsType = Table.TransformColumnTypes(ExpandDates,{
{"Date", type date},
{"Daily Expense", type number}})
in
ChangeFiledsType```

Close and load the table into data model with connection only

Not to create time groups manually be sure in Excel File->Options->Data automating grouping is not disabled

Stay on empty cell and in ribbon Insert->PivotChart, add Dates to axis and Daily Expenses to values.

Now to do some cosmetic with generated chart, result will be like this

You may drill up/down calendar periods using +/- buttons at the bottom/right.

And in attached file.