Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
ShiftEvents Table
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!
Solved! Go to Solution.
Hi @fjmocke,
I would suggest you calculate Availability via calculated column.
In ShiftEvents Table, please add:
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
Hi @fjmocke,
I would suggest you calculate Availability via calculated column.
In ShiftEvents Table, please add:
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
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |