Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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!
Before you try to re-invent the wheel, I'd kindly suggest reading this: Standard time-related calculations – DAX Patterns
@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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |