creating a rolling measure on top of another rolling measure

[Anonymous]

I have a scenario where I am plugging numbers into an equation to calculate a safety rating.

My client works in 13 periods in a year, 28 days (roughly) each in a year rather than months . So time intelligence functions wont work here. 

So the measure is supposed to calculate fatality rate over number of hours  in a 28 day period. The trouble is due to data quality problems, ocassionally those hours dont come through, theyre blank. So to mitigate this  we take an AVERAGE of the hours worked in the previous 6 periods and use that in calculation.

Furthermore I am required to calculate a rolling  average which might be based on this scenario.

Here are my dax formulas:

//This applies in scenario where there are no hours in the current period displaying

TotalHoursAdjusted =

Var currentPeriod = SELECTEDVALUE('PeriodLookup'[Period index])

return

COALESCE(IF([TotalHours_KPIData] = 0,CALCULATE(DIVIDE([TotalHours_KPIData],6),filter(ALL('PeriodLookup'),'PeriodLookup'[Period index]>=(CurrentPeriod-6) && 'PeriodLookup'[Period index]<currentPeriod)),0),0)

//Rolling 13 period hours

RollingAdjustedhours =

VAR CurrentPeriod = SELECTEDVALUE('PeriodLookup'[Period index])

VAR adjusted = [TotalHoursAdjusted]

return

COALESCE(CALCULATE([TotalHoursAdjusted],FILTER(ALL('PeriodLookup'),'PeriodLookup'[Period index]<=CurrentPeriod && 'PeriodLookup'[Period index]> (CurrentPeriod-13))),0)

As you can see because they are both applying their filter contexts in table periodlookup (essentially my date table) the whole thing falls apart. I am not sure the best way to approach this. It seems like I need a temporary store for those adjusted hours somewhere. I tried to create a table variable but then I get an error telling me that the filter context cannot be applied between period lookup and my temporary table. It also seems like a computationally expensive way to calculate. Any ideas? Your input would be greatly appreciated

[amitchandak]

@Anonymous , for all such case where you do not have a regular period , you have follow the approach , same I discussed in Week blog

You need period in YYYYPP format

YYYYPP = [Year]*100 + [period]

In case you have a period start that you can use that you do not need YYYYPP

This true for period . Make sure you have a separate period table

Column

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)  //or use period start date

measures
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Last 6 Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]>=max('Date'[Period Rank])-6 && 'Date'[Period Rank]<=max('Date'[Period Rank])))

For year Column

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Day of Year =datediff([Year Start date] , [Date],Day) +1

Measures
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

[Anonymous]

Hi,

I think you misunderstand. I have successfully created YTD, last 6 months , last 13 periods (for one year) The problem I face: is when I have a measure (x) that is building data based upon last 6 periods and then another measure on top of that (y) which uses (x). Measure (y) goes back 13 periods. Measure (x) works on its own but when I calculate (y) based on (x) it fails because they are both using the same period table field and the context gets messed up. So I need to find a way of temporarily storing (x) so I can workout (y)