12 Month Rolling Calculation

[Anonymous]

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

 

[Greg_Deckler]

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-TIT...

[Anonymous · Accepted solution]

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)
                 )