Rolling 12 month Sum DAX - Ignore all date filters

Question

I've been working on writing a dax so that I can get the sum sales amount for the last 12 calendar months. I've played with ALL and KEEPFILTERS but I still can't seem to get it. Below is an example of what I've tried. But I don't want just 365 days back.. I would like 12 Calendar Months (or I would settle for going back to the 1st day of 12 calendar months and then ending on today). IE: Today it would be 1/1/19 to 2/6/2020 '''

var varToday =  Today()
var varYearAgo = varToday - 365
RETURN


CALCULATE(
    SUM(FACT[Earnings]),
    FILTER('Earnings',
 'Earnings'[PayDate] <= varToday
 && 'Earnings'[PayDate]>= varYearAgo
))

'''

Below is howt he data looks

I have added a calendar table as well: DimCalendar and attempted this as my DAX

'''

var varEarnings = SUM(Fact[Earnings])



RETURN 


CALCULATE (
   varEarnings ,
    ALLEXCEPT ( DIMCalendar, DIMCalendar[Date] ),
    DATESINPERIOD( DIMCalendar[Date], today(), -12, MONTH )
)

'''

You should add a Calendar table to you data model, and then use time intelligence functions like DATESINPERIOD or DATESBETWEEN. — RADO
ALLEXCEPT could used here to ignore filter context. if you have an data example that would help — Jon

CR7SMS CR7SMS · Accepted Answer · 2020-02-07T15:39:18

To get the date filter you are looking for, you can simply using the edate function to subtract months from the date:

Rolling Sum = 
VAR varToday = TODAY()
VAR varYearAgo = DATE(YEAR(EDATE(End1,-13)),MONTH(EDATE(End1,-13)),1)
RETURN
CALCULATE(
    SUM(FACT[Earnings]),
    FILTER('Earnings','Earnings'[PayDate] <= varToday),
    FILTER('Earnings','Earnings'[PayDate] >= varYearAgo),
))

To remove the filters applied you would have to use ALL() or ALLEXCEPT() or ALLSELECTED() in your calculation. The usage will vary depending on the specific context. Hope this helps.

Rolling 12 month Sum DAX - Ignore all date filters

1 Answers