Compare cumulatve donations year on year

[willjsaunders]

Hi all,

I'm pretty new to this, so please bear with me.

I have data for donations going back to 2012 and have successfully created a new measure to plot cumulative donations over that time using this DAX:

Cumulative Gift FY21 =
CALCULATE(
SUM(Gift[amount]),
FILTER(
ALL(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])))

This gives me a nice chart with a single line creeping upwards from 2012 (on the left of the x axis) up until the present day (on the right).

But what I really want is the x-axis running from August on L to July on R (our FY) with 10 lines on the chart (for 2012 to 2021 inclusive) to allow comparison of progress against other years. (I have already offset the months in my date table and have a 'Fiscal Year' column sorted correctly).

How should I go about this? I presume I will need a new measure for each year which I can then map onto the visual (please correct me if I'm wrong) but I cannot figure out the DAX for this. I would really appreciate some idiot-proof help 🙂

Many thanks,

Bill

[willjsaunders]

Brilliant! This worked a treat. Thank you 🙂 

[Jihwan_Kim]

Hi, @willjsaunders 

Please correct me if I wrongly understood your question.

If you have Fiscal Month Name and Fiscal Year Columns in your dim-date table, try to put the Fiscal month column on X-axis and the Fiscal year column in the legend field.

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate solution.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[willjsaunders]

Hi Jihwan,

Thank you so much for your help. Your suggestion has indeed helped! I am now able to see this, which is a big improvement: 

Ideally each line would start at 0 at the beginning of August, though - so it would be easier to compare progress at any point in the FY. Is there any way of doing this without creating a measure for each year?

Another solution I'm working on is by creating a measure for each FY using this DAX:

Cumulative Gift FY20 =

CALCULATE(

SUM(Gift[amount]),

DATESINPERIOD(Dates[Date],

DATE(2019, 08, 01),

1, YEAR))

But when I plot it to a line chart I get flat lines like this:

Any help very gratefully received! I'm thoroughly enjoying the learning curve, but my goodness there's a lot of functionality for someone relatively new to get their head round!

Thanks again,

Bill

[Jihwan_Kim · Accepted solution]

Hi, @willjsaunders 

Thank you for your feedback.

I suggest trying TOTALYTD Dax function.

Because this gives you the cumulative sum and starts over every year. Furthermore, if your fiscal year starts every August, you can also select this as an option.

Please check the below link that explains about TOTALYTD DAX function.

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM