Cumulative calculation when Year End is not December

DAX DAX Calculations
1

Hi everyone,

I have tried to use the cumulative calculation for revenue and expenditure and it generally works fine except the fact that my financial year is April to March. When I use the YTD function, it gives me the option to state the end of the year date (i.e. “31/03”) and as such, my cumulative starts from April till March the following year.

I would like to know how to achieve the same thing by using the cumulative calculation as suggested in the training material in here by @sam.mckay.

Thanks

2

The best thing to do here is create a column in your date table with the Financial Years in it.

This can be done with the standard date table code provided here.

http://portal.enterprisedna.co/courses/103686/lectures/2972958

As this cumulative total pattern is dynamic, all you then need to do is have a slicer on your page that filters by financial years.

So, there is no special formula for this really. It’s just about using the cumulative pattern you already know then placing the correct filters over it.

See below for an example file that has the exact scenario I’ve just mentioned.

3

Hi,

Thank you for getting back on this. I already have the full calendar, including the year. The issue I have is the opening balance at the start of the year. Whilst the chart does show the months in the order of FY the starting point is not zero.

4

Is it possible to add a demo file of the scenario for this. I’m finding it difficult to imagine the exact issue here.

Thanks

5

Would a screenshot of the file be ok?

6

Sure we can try to work with this, but a replica demo model would be best.

I don’t believe this is too difficult, but I’m just not understanding the exact thing you require at the moment

7

Hi,

Apologies for disappearing. I found it out. It was due to some issue with my calendar link to the data and now it is all sorted. Thanks!