Rolling 12 Month sum that ignores date filter

[Anonymous]

Hi, I'm wanting to create a DAX equation that ignores the date filters (But still lets people filter by other slicers) and return a sum earnings for the 12 month range.  Going back 12 Calendar Months.  I've tried the below DAX But I am not having any luck.

Below is the DAX I have put together. However when I use this and try to uset he date slicer in my dashboard this still filters.

var varEarnings = SUM(Fact[Earnings])



RETURN 


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

Table looks something like this:
I would like it to always sum what is in yellow no matter what someone selects in the date slicer.  They can use another slicer which is Location

[amitchandak]

I do not think you need all except. I use like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Thanks @amitchandak ,

This looks like it gets me halfway.  So i noticed that if I move the from date filter nothing changes.  But if I move the end date filter the #'s will reduce.  Is there a way to lock both sides of the filter down to always use today's date as it's start and go back 12 calendar months?

Thanks,
Jon

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure

Measure =
CALCULATE (
    SUM ( Sheet2[value] ),
    FILTER (
        ALL ( 'calendar'[Date] ),
        DATEDIFF (
            'calendar'[Date],
            TODAY (),
            MONTH
        ) >= 1
            && DATEDIFF (
                'calendar'[Date],
                TODAY (),
                MONTH
            ) <= 12
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.