Forum Discussion
Plotting multiple cumulative totals from different years on the same graph
Hello,
I have three measures that that plot the cumulative income raised during and Financial year. They will plot well on a graph that contains a filter for the respective finacial year. However, I want to merge the three graphs into one containing the multiple lines, and I can't seem to get the DAX to remove the date filters in the correct way to make this work, keeping a filter on the month.
The three cumulative income income measures are in the following format:
But when I try to plot them on a single graph without the Finacial Year Filters I get something like:
I suspect I need to tweak the use of the ALL filter in the measures so that the lines still plot correctly against the month, cumulativly. I can't seem to work this out. Maybe using Keepfilters on the month column?
Greatful if someone could point me in the right direction.
Ideally the line for the current year would stop at the current month rather than projecting forward to the end of the year as in the pink line above.
Many thanks,
Adam
or
12 Replies
Yes, you need to eliminate the year part from the measure and move it to the legend as its own materialized measure.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216Hi lbendlin,
Thanks for engaging with this.
The data model for this is quite simple. I have a single data table in excel - called Table 2 and I've added a fairly standard calendar table in power BI which does the financial year grouping.
Dummy data for table 2 cut down for just this query would look something like:
Date Amount (£) 01/01/2022 100 01/02/2022 200 01/03/2022 100 01/12/2022 300 01/01/2023 500 01/04/2023 100 01/08/2023 250 01/09/2023 300 01/12/2023 400 01/01/2024 500 01/02/2024 500 01/05/2024 1000 01/07/2024 400 01/09/2024 850 01/12/2024 900 You can slightly modify the "Running Total" Quick Measure.
