Forum Discussion
12 Month Rolling Calculation
Background forum on how I got my initial measure https://community.powerbi.com/t5/Desktop/Having-Clause-or-something-close/m-p/944351#M452558
So I'm trying to get a 12 month rolling calculation for my measure by Year/Mo. I expected to get 88 at legal entity = '4' and year/mo = '2019/12'. Instead I'm getting only 1. I checked my SQL tables and in December there was only 1 record. I can't figure out how to get my DAX to ignore the year/mo filter applied by visual.
Pure_CEMI_Customers =
var _12MoStartDate = DATE(YEAR(MAX('Date Table'[Date])), MONTH(MAX('Date Table'[Date])) -11, 1)
var _12MoEndDate = DATE(YEAR(MAX('Date Table'[Date])), MONTH(MAX('Date Table'[Date])), DAY(MAX('Date Table'[Date])))
var _count = CALCULATE(COUNT(Outages[CASE_ID]))
var Pure_CEMI_Customers =
COUNTROWS(
FILTER(
VALUES(Outages[Account_NBR]), -- gets a distinct list of account_nbr
-- forces a context transition so that Case_ID is
-- filtered for just those under the current Account_nbr
CALCULATE(
COUNT(Outages[CASE_ID]), ALLSELECTED('Date Table'), DATESBETWEEN('Date Table'[Date], _12MoStartDate, _12MoEndDate)
)
> SELECTEDVALUE(CEMI_Threshold[CEMIn], 9)
)
)
return Pure_CEMI_Customers
I figured out a solution. Instead of duing my 12mo rolling in the same measure, I created a new measure and did it there. Don't know why but it fixed the issue.
12Month Rolling = var enddate = MAX('Date Table'[Date]) var startdate = EDate(enddate, -11) return calculate( [pure_cemi], all('Date Table'), datesbetween('Date Table'[Date], startdate, enddate) )
2 Replies
To ignore filters use REMOVEFILTERS or something like ALL or ALLEXCEPT. There is a rolling quick measure calculation. Also, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008I figured out a solution. Instead of duing my 12mo rolling in the same measure, I created a new measure and did it there. Don't know why but it fixed the issue.
12Month Rolling = var enddate = MAX('Date Table'[Date]) var startdate = EDate(enddate, -11) return calculate( [pure_cemi], all('Date Table'), datesbetween('Date Table'[Date], startdate, enddate) )
