Chart of expenses over time

[Kaloyan Stoyanov]

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

[#2]

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?

[#3]

Hi Sergei,
Distributed proportionally, please

[#4]

Proportionally to calendar days?

[#5]

Hi,

This is going to be analyzed on quarterly and annual basis. I'm perfectly happy with just a quarterly breakdown though.

[#6 · Accepted solution]

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.

[#7]

Hi @SergeiBaklan,

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