cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sshokri89
Helper I
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)

sshokri89_0-1631774228171.png

 

 

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
sshokri89
Helper I
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.

View solution in original post

4 REPLIES 4
sshokri89
Helper I
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.

View solution in original post

sshokri89
Helper I
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:

sshokri89_0-1631777847909.png

 

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

 

sshokri89_1-1631777888357.png

 

 

still wondering why!

daxer
Solution Sage
Solution Sage

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

amitchandak
Super User
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!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors