Cumulative Formula with year split not working

[Jayne_B]

Hello!

I've been trying to write a cumulative forumla to add the total number of incidents by site by month. With these results I want to plot a line chart from Jan to December with the three years of data I have i.e. have 2019 as a line, 2020 as another and 2021 as the third. I've written a forumal based off of suggestions from other posts however I just cannot get it to work.

Below is what I've done and the output followed. Any help would be greatly appreciated, been looking at this for hours 🤣.

Matrix showing date hierarchy:

Total incidents is a measure built using:

And RunningTotal using:

As you can see the cumulative doesn't do anything different to the Total Incidents. However when I plot this on a line graph this is what I get:

I did also try to another forumula, but the results gave me like for like on total incidents:

[v-kelly-msft]

Hi @Jayne_B,

Is your issue solved now?

If not,could you pls provide your .pbix file or some sample data?We would be pleased to modify the measure for you.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

You must have a Calendar Table with a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number into seperate columns.  Sort the Month Name by the Month number.  To your visual, drag Year and Month from the Calendar Table.  Assuming Total incidents is a measure, write this measure to get the running total which restarts every year

Running total = calculate([total incidents],datesytd(calendar[date],"31/12"))

Hope this helps.

[selimovd]

Hey @Jayne_B ,

if you don't have a date table create one and connect it with your fact table:

How to create a date table in Power BI in 2 simple steps - Kohera

Then the formula is pretty easy:

RunningTotal =
CALCULATE(
    [Total Incidents],
    DATESYTD( DateTable[Date] )
)

By the way, I've seen the writing with "+0" many times, like in the [Total Incidents] measure. I never understood why would you add 0 to something. I would be happy to understand that if you want to explain that 🙂 

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic