cancel
Showing results for
Did you mean:
Helper I

Cumulative Sum of the last 12 calendar months vs its prior 12 calendar months

Hi all,

I have a requirement to write a measure where I can compare the Cumulative sum of my last 12 calendar months vs its prior 12 calendar months. I can't see to get the correct numbers anyway I do it!

here is what I have in excel (this data is one month old, so july is the last calendar month if we consider last month)

As can be seen, July is our last calendar month and the data goes back 12 months from there, and calcualtes the CURRENT YEAR data. then in excel, I have PRIOR YEAR which gives me value of the previous 12 months. e.g. July 2021 vs July 2020... October 2020 vs October 2019.

Now to calculate the CUMULATIVE SUM, in excel, each months value has simply been added to the previous months value. e.g. Aug2020 Current year = 31192 and prior year (august2019) = 37476. and Sep 2020' cumulative sum is  31192+34234=65426 and 37476+37797=75273 for its prior year value.

Update:
This is a running total measure not TOTALTYD type.

Thanks in advance 🙂
1 ACCEPTED SOLUTION
Helper I

For some reason, the measures you gave to me didn't work. then I end up creating two measures like this which actually worked fine after a few trial-error:

Rolling12_CY = CALCULATE(SUM('Sales'[unit]),DATESINPERIOD(DateTable[Date],MAX(DateTable[Date]),-12,MONTH),DateTable[Year_Classification] = "Current Year")

note that my I already had a "current year" column in my date table to refer to the past 12 months starting from the last day of previous month.

Rolling12_LY_v2 =
VAR EndDate =
EOMONTH(MAX(DateTable[Date]),-12)

VAR StartDate =
EDATE( EndDate, -12 ) + 1 -- shifts EndDate to year beginning

VAR Result =
CALCULATE(
SUM('Sales'[Unit]),
DATESBETWEEN( DateTable[Date], StartDate, EndDate ),
DateTable[Year_Classification] = "Last Year"
)
RETURN
Result

Both of these measures are now returning correct values. So I'll mark this post as solution in case anyone has the same issue. but thanks all who contribute to the post.

4 REPLIES 4
Helper I

For some reason, the measures you gave to me didn't work. then I end up creating two measures like this which actually worked fine after a few trial-error:

Rolling12_CY = CALCULATE(SUM('Sales'[unit]),DATESINPERIOD(DateTable[Date],MAX(DateTable[Date]),-12,MONTH),DateTable[Year_Classification] = "Current Year")

note that my I already had a "current year" column in my date table to refer to the past 12 months starting from the last day of previous month.

Rolling12_LY_v2 =
VAR EndDate =
EOMONTH(MAX(DateTable[Date]),-12)

VAR StartDate =
EDATE( EndDate, -12 ) + 1 -- shifts EndDate to year beginning

VAR Result =
CALCULATE(
SUM('Sales'[Unit]),
DATESBETWEEN( DateTable[Date], StartDate, EndDate ),
DateTable[Year_Classification] = "Last Year"
)
RETURN
Result

Both of these measures are now returning correct values. So I'll mark this post as solution in case anyone has the same issue. but thanks all who contribute to the post.

Helper I

Thanks for your help. I had this measure before:

CY Month running total in Date =
Var LY = DATEADD(DateTable[Start of Month],-1,YEAR)
RETURN CALCULATE(
SUM('Sales By Buyer Type_xlsx'[CY Month]),
DATESINPERIOD(DateTable[Date],MAX(DateTable[Date]),-12,MONTH),FILTER(ALLSELECTED(DateTable[Date]),  (DateTable[Date]) >= LY ))

and the one you gave me (rolling 12) is doing the same. I need to see why. the issue is that if I select a month e.g. July it shows correct Cumulative Sum values but once all my months are listed in the table, it shows all wrong values:

but if I filter my months using a slicer, it shows the correct values for those months:

still wondering why!

Solution Sage

Before you try to re-invent the wheel, I'd kindly suggest reading this: Standard time-related calculations – DAX Patterns

Super User

@sshokri89 , Try measure like examples

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-12) ,-12,MONTH))

or

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxX('Date', dateadd('Date'[Date],-12,month)),-12,MONTH))

use -13 if needed in in datediff or eomonth if needed

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements