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.