Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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!

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.