Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |