Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |