Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
i have data that is cumilated year and month wise . i am asked to show rolling 12 months sum . can you please guide me how it can be acheived .
i have previously worked with a date table and calculated rolling sum . but here i dont have any date values .
This data is having year and month .
by using
YEAR | MonthNumber | MonthName | TotalHours | TotalInjuries | IncidentRate | rolling 12 of incident rate | ||
2022 | 1 | Jan | 12 | 3 | 50000 | |||
2022 | 2 | Feb | 13 | 1 | 15384.61538 | |||
2022 | 3 | Mar | 14 | 1 | 14285.71429 | |||
2022 | 4 | Apr | 15 | 2 | 26666.66667 | |||
2022 | 5 | May | 16 | 0 | 0 | |||
2022 | 6 | Jun | 17 | 1 | 11764.70588 | |||
2022 | 7 | Jul | 17 | 0 | 0 | |||
2022 | 8 | Aug | 17 | 1 | 11764.70588 | |||
2022 | 9 | Sep | 17 | 1 | 11764.70588 | |||
2022 | 10 | Oct | 17 | 1 | 11764.70588 | |||
2022 | 11 | Nov | 17 | 0 | 0 | |||
2022 | 12 | Dec | 17 | 0 | 0 | |||
2023 | 1 | Jan | 17 | 1 | 11764.70588 | 115160.5257 | excepted result | |
2023 | 2 | Feb | 17 | 1 | 11764.70588 | |||
2023 | 3 | Mar | 17 | 0 | 0 | |||
2023 | 4 | Apr | 17 | 1 | 11764.70588 | |||
2023 | 5 | May | 17 | 0 | 0 | |||
2023 | 6 | Jun | 17 | 0 | 0 | |||
2023 | 7 | Jul | 17 | 1 | 11764.70588 | |||
2023 | 8 | Aug | 17 | 1 | 11764.70588 | |||
2023 | 9 | Sep | 17 | 0 | 0 | |||
2023 | 10 | Oct | 17 | 2 | 23529.41176 | |||
2023 | 11 | Nov | 17 | 2 | 23529.41176 | |||
2023 | 12 | Dec | 17 | 1 | 11764.70588 |
Solved! Go to Solution.
@KrishnaNags
@KrishnaNags
Add a new column to your table to create a Year+Month
YearMonth = 'Table'[YEAR] * 100 + 'Table'[MonthNumber]
Create the following measure:
Rolling 12M Incident Rate =
VAR __Relation =
ADDCOLUMNS (
ALLSELECTED (
'Table'[YEAR],
'Table'[MonthName],
'Table'[MonthNumber],
'Table'[YearMonth]
),
"@IncRate", [Total Incident Rate]
)
VAR __Window =
WINDOW ( -11, REL, 0, REL, __Relation, ORDERBY ( 'Table'[YearMonth] ) )
VAR __Result =
CALCULATE ( [Total Incident Rate], __Window )
RETURN
IF ( COUNTROWS ( __Window ) >= 12, __Result )
Here is the result, file attached below.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@KrishnaNags
@KrishnaNags
Add a new column to your table to create a Year+Month
YearMonth = 'Table'[YEAR] * 100 + 'Table'[MonthNumber]
Create the following measure:
Rolling 12M Incident Rate =
VAR __Relation =
ADDCOLUMNS (
ALLSELECTED (
'Table'[YEAR],
'Table'[MonthName],
'Table'[MonthNumber],
'Table'[YearMonth]
),
"@IncRate", [Total Incident Rate]
)
VAR __Window =
WINDOW ( -11, REL, 0, REL, __Relation, ORDERBY ( 'Table'[YearMonth] ) )
VAR __Result =
CALCULATE ( [Total Incident Rate], __Window )
RETURN
IF ( COUNTROWS ( __Window ) >= 12, __Result )
Here is the result, file attached below.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group