Probably a simple one! Average over previous 12 months and filter using date slicer?

[themaidenmaniac]

Hi guys,

Quite new to power BI and trying to come up with a basic formula. I have a dates table but cant seem to get this to work with the filters.

The idea is that you select your month from using a slicer, and it then uses the data 12 months previous to that selection to calculate the average. Here's what i thought would work so far, the sum to create the average and then a filter that selected the previous 12 months from my date table. That works fine, but if you put a date slicer on it kills it. Appreciate your guidance as i'm quite new and struggling!

I have a feeling i might have to input some code to link a slicer but i'm not sure about that. 

Test measure =

CALCULATE

(

DIVIDE

(

    SUM('IT_Opex_Cost_Savings'[Realised Cost Savings £]),

    SUM('IT_Opex_Cost_Savings'[Forecasted Cost Savings £])

),

'Date'[Calendar RelativeMonthPos] IN {0,1,2,3,4,5,6,7,8,9,10,11,12}

)

[jdbuchanan71 · Accepted solution]

First I would write two measures, one for Realised and one for Forcasted:

• RealisedCostSavings = SUM('IT_Opex_Cost_Savings'[Realised Cost Savings £])
• ForecastedCostSavings = SUM('IT_Opex_Cost_Savings'[Forecasted Cost Savings £])

Then you can use some variables and your new measure to calculate the ratio.  I believe the problem you were running into is your date table was following the filter context when you picked a month becasue you had not opened it up in your measure.

Avg 12 months = 
VAR MaxDay = MAX ( 'Date'[Date] )
VAR EarliestDay = EOMONTH(MaxDay,-12)+1

RETURN
    CALCULATE( DIVIDE ( [RealisedCostSavings] , [ForecastedCostSavings] , 0) ,
        FILTER( ALL ( 'Date' ),
        'Date'[Date] >= EarliestDay &&
        'Date'[Date] <= MaxDay
        )
    )

The variables return the last date you have seleted (based on your slicer) and the first day of the month 11 months ago so you get a full 12 month window then those are used to filter the date table inside the CALCULATE

[themaidenmaniac]

Very kind of you sir!! Super appreciated for taking the time. I'll give that a bash and let you know how it goes! 🙂