Help with cumulative sum by month year with a category filter

[zuber85]

Hi,

I am a newbie to Powerbi and want to create a line chart graph that shows a cumulative total broken down by years but the graph would show it on a monthly, I also would like to have a filter option that when selected would only show me the figures for that category.  See table below:

Date	category	profit
01/01/2023	1	966
07/01/2023	2	825
10/01/2023	1	413
05/02/2023	1	468
05/02/2023	1	692
07/03/2023	2	404
14/03/2023	1	341
14/05/2023	2	391
19/05/2023	1	300
19/05/2023	3	323
19/05/2023	1	503
20/06/2023	2	322
20/06/2023	2	441
17/07/2023	1	930
19/07/2023	1	237
02/09/2023	2	368
10/10/2023	1	793
11/11/2023	2	349
13/11/2023	1	756
12/12/2023	1	932
24/12/2023	2	692
01/01/2024	1	273
07/01/2024	2	849
10/01/2024	2	770
05/02/2024	1	898
05/02/2024	1	104
07/03/2024	1	450
14/03/2024	2	339
14/05/2024	1	207
19/05/2024	2	703
19/05/2024	1	405
19/05/2024	3	502
20/06/2024	1	580
20/06/2024	2	352
17/07/2024	1	552
19/07/2024	1	624
02/09/2024	1	493
10/10/2024	2	398
11/11/2024	2	299
13/11/2024	1	407
12/12/2024	1	698
24/12/2024	1	911

So the Chart would show months at the bottom, amounts on the left and would have a line per year as a cumulative sum, I can then filter by the category

I hope I have made some sense here. 

[Corey_M · Accepted solution]

Datetables are a very common theme in powerBI and will help you greatly here. 

here is a sample table that might help get you started (https://chandoo.org/wp/power-query-calendar-table-best-method/ )

let
    Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
    #"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
    #"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
    #"Removed Columns"

one you have a datetable setup, create a relationship between the dates in your table and the datetable.

then just create a linechart with Month in the X-axis, Sum of Profit in the Y-axis, and Year in the Legend

The last thing is to add a slicer which you can put the category in allowing you to filter to that category. 

[v-junyant-msft · Accepted solution]

Hi @zuber85 ,

@Corey_M Good Answer! But I think you forget to calculate the cumulative total broken down by years, you just calculate the total of each month of each year.

And @zuber85  If you don't need all months showed at the bottom, you can either use the date hierarchy that comes with Power BI Desktop directly or use DAX:

Month = MONTH('Table'[Date])

And I choose to add a new Table Slicer for filter by the category (In order to remove some of the effects on filtering, I would suggest adding a new table) :

Then use this DAX to create a measure to calculate the cumulative total broken down by years:

cumulative total broken down by years = 
CALCULATE(
    SUM('Table'[profit]),
    ALL('Table'),
    YEAR('Table'[Date]) = YEAR(MAX('Table'[Date])) && MONTH('Table'[Date]) <= MONTH(MAX('Table'[Date])) && 'Table'[category] IN VALUES('Slicer'[category])
)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zuber85]

@v-junyant-msft @Corey_M 

Thank you very much.

As a newbie took me a little time, but got there. 

[Corey_M · Accepted solution]

Datetables are a very common theme in powerBI and will help you greatly here. 

here is a sample table that might help get you started (https://chandoo.org/wp/power-query-calendar-table-best-method/ )

let
    Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
    #"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
    #"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
    #"Removed Columns"

one you have a datetable setup, create a relationship between the dates in your table and the datetable.

then just create a linechart with Month in the X-axis, Sum of Profit in the Y-axis, and Year in the Legend

The last thing is to add a slicer which you can put the category in allowing you to filter to that category.