Plotting multiple cumulative totals from different years on the same graph

[adamlang]

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:

Total Income Raised (Cumulative) during 2023/24 =

VAR MaxDate = MAX('Calendar'[Date])

VAR Result =

    CALCULATE(

        [Total Value Secured],

        'Calendar'[Date] <= MaxDate,

        ALL('Calendar'),

        'Calendar'[BBBC Financial Year] = "2023/24"

    )

RETURN

Result

Where [Total Value Secured] =

Total Value Secured =

    CALCULATE(SUM(Table2[BBBC Income (All Years) ]), Table2[Final Application Submitted] = "Secured" || Table2[Final Application Submitted] = "Project Closed" || Table2[Final Application Submitted] = "Closed with extension", USERELATIONSHIP(Table2[Decision Date (Expected moving to Actual Date)], 'Calendar'[Date]))

The three indiviual graphs look like:

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

[lbendlin]

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-...

[adamlang]

Hi @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

[lbendlin]

You can slightly modify the "Running Total"  Quick Measure.

[adamlang]

Hi @lbendlin,

Thanks for this I really appreciate the support. It's close but actually I need for each new year (e.g. finacial year) brought into the legend (i.e. for each line) to start again from zero and cumulate from their, rather than continue from the previous year.

Any suggestions?

Adam

[lbendlin]

That's what you see.  It is based on the sample data you provided.

 start again from zero 

How?  Do you have a month zero?  Even with day level granularity it wouldn't start at zero.

[adamlang]

So I'd like for each year to start again from zero, and build up from there. Like the three graphs in my original post. The graphs as plotted continue from the point of the previous year, and add the next month.

So each line should begin from zero.

It's more complicated though because its the calandar table which determines what kind of year is being used, i.e. which field its brought into the legend. It could be Calendar year, UK Financial Year, and my organisation has its own accounting year which starts in July.

Is there any way to code the DAX so any of those types of year could be moved to the legend and the correct lines would plot - I'd just need to re-sort the month order so the the year starts at the right month. If not then just the organisational accounting year would be fine. which is called BBBC Financial Year, in my Calendar table, if that's helpful.

Many thanks,

Adam

[adamlang]

Hi @lbendlin, 

So maybe 'starting from zero' isn't the clearest way of explaining this need.

If we look at Jan 2024, currently in the graph this is plotted against a figure of 2,750 - the sum total of all the income before that date. I need the figure for Jan 24 to be 500, the total income for the relevant year, for Feb 24 it would be 1,000, (the Sum of Jan 24 and Feb 24) and so on...

Hope helps explain the requirement. Thanks again for your support.

Many thanks,

Adam

[lbendlin]

[adamlang]

Hi @lbendlin,

Thanks!

That works just fine for a standard calendar year starting in January, but my organisation's year runs June to July, and when I try to run plot the same measure against that finacial year (using a different Month Sort column), it still 'resets' to zero in Janurary, so it looks like this:

I suspect this is the YEAR filter being used, the final filter condition. Is there an alternative that would match the Financial year, i.e. 'resets' in July each year?

As I say it does work correctly for a Jan-Dec Year:

Thanks,

Adam

[lbendlin]

Use a Calendar table, clearly indicate the Fiscal year in it, and use that column in the running total measure.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[adamlang]

@lbendlin Thanks again for your continued support.

You've asked me a couple of times now to put this all in a model, and my apoligies for not doing so sooner. Please find this at the link below - I think OneDrive loads power bi files with a error saying no preview - but there should be a button to download it.

https://1drv.ms/u/c/321ec98b1e1d23b9/EaRxpKYSvh9Oicpdzdp3DFwBTwfxwPnWe6zOp5v1NGzcDQ?e=4%3aBQgtfo&sha... 

I've added my default Calendar (a DAX table) and updated the measure to use the date field in this table - Amount (£) running total in Date v2.

You'll see the behaviour I'm talking about in the visual on page 2, at the month of January the lines start again, whereas in this graph the year starts in July.

I have three years that I use - Calendar Year, BBBC Financial Year and UK Financial Year. Ideally I'd like the measure to work with them all, but if only one is possible then its BBBC Financial Year I need.

Many thanks,

Adam

[lbendlin · Accepted solution]

or