cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 12 Month Moving Average of Measure with filter

I have tried multiple online sources to build a 12 month roling average, with no success.  The 'New Quick Measure' option also did not work for me.

I have a measure called Availability.  The code is as follows:

```Availability = CALCULATE(SUM(ShiftEvents[DurationInHours]),FILTER(ShiftEvents,ShiftEvents[Level_3_Code]="T200"))
/
CALCULATE(SUM(ShiftEvents[DurationInHours]),FILTER(ShiftEvents,ShiftEvents[Level_2_Code]="T100"))```

The following two tables exist:

Shifts Table

• ShiftDate
• MonthDate

ShiftEvents Table

• ShiftDate,
• DurationInHours
• Leve_1_Code,
• Leve_2_Code,
• Leve_3_Code,
• Leve_4_Code,
• Leve_5_Code

The underlined columns have a one to many relationship.

'ShiftEvents' table has a date column called 'ShiftDate'.  This column is linked to the 'ShiftDate' column in a a table called 'Shifts'.  The 'Shifts' table is where the 'MonthDate' column exists in order to aggregate/slice data via Month.

In summary, I need to create a measure that takes the data from Availability, and construct a 12 month moving average by using the 'MonthDate' column in the 'Shifts' table.

Any help will be appreciated!

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @fjmocke,

I would suggest you calculate Availability via calculated column.

```MonthNo = MONTH('ShiftEvents Table'[ShiftDate])

Availability col =
(
CALCULATE (
SUM ( 'ShiftEvents Table'[DurationInHours] ),
FILTER (
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] ),
'ShiftEvents Table'[Leve_3_Code] = "T200"
)
)
/ CALCULATE (
SUM ( 'ShiftEvents Table'[DurationInHours] ),
FILTER (
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] ),
'ShiftEvents Table'[Leve_2_Code] = "T100"
)
)
)
/ CALCULATE (
COUNT ( 'ShiftEvents Table'[ShiftDate] ),
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] )
)```

Then, create measure like below:

```Moving average availability =
CALCULATE (
SUM ( 'ShiftEvents Table'[Availability col] ),
FILTER (
ALL ( 'Shifts table' ),
'Shifts table'[MonthDate] <= MAX ( 'Shifts table'[MonthDate] )
&& 'Shifts table'[MonthDate]
> MAX ( 'Shifts table'[MonthDate] ) - 3
)
)
/ 3```

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

Hi @fjmocke,

I would suggest you calculate Availability via calculated column.

```MonthNo = MONTH('ShiftEvents Table'[ShiftDate])

Availability col =
(
CALCULATE (
SUM ( 'ShiftEvents Table'[DurationInHours] ),
FILTER (
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] ),
'ShiftEvents Table'[Leve_3_Code] = "T200"
)
)
/ CALCULATE (
SUM ( 'ShiftEvents Table'[DurationInHours] ),
FILTER (
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] ),
'ShiftEvents Table'[Leve_2_Code] = "T100"
)
)
)
/ CALCULATE (
COUNT ( 'ShiftEvents Table'[ShiftDate] ),
ALLEXCEPT ( 'ShiftEvents Table', 'ShiftEvents Table'[MonthNo] )
)```

Then, create measure like below:

```Moving average availability =
CALCULATE (
SUM ( 'ShiftEvents Table'[Availability col] ),
FILTER (
ALL ( 'Shifts table' ),
'Shifts table'[MonthDate] <= MAX ( 'Shifts table'[MonthDate] )
&& 'Shifts table'[MonthDate]
> MAX ( 'Shifts table'[MonthDate] ) - 3
)
)
/ 3```

Regards,

Yuliana Gu

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