Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KrishnaNags
Frequent Visitor

How to get rolling 12 months sum from year wise and month wise data

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 

DATESINPERIOD is not working .
 
sample data 
YEARMonthNumberMonthNameTotalHoursTotalInjuriesIncidentRaterolling 12 of incident rate  
20221Jan12350000   
20222Feb13115384.61538   
20223Mar14114285.71429   
20224Apr15226666.66667   
20225May1600   
20226Jun17111764.70588   
20227Jul1700   
20228Aug17111764.70588   
20229Sep17111764.70588   
202210Oct17111764.70588   
202211Nov1700   
202212Dec1700   
20231Jan17111764.70588115160.5257excepted result 
20232Feb17111764.70588   
20233Mar1700   
20234Apr17111764.70588   
20235May1700   
20236Jun1700   
20237Jul17111764.70588   
20238Aug17111764.70588   
20239Sep1700   
202310Oct17223529.41176   
202311Nov17223529.41176   
202312Dec17111764.70588   
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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.

Fowmy_0-1702124033994.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
KrishnaNags
Frequent Visitor

@Fowmy Thank you !! This Definately works 

Fowmy
Super User
Super User

@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.

Fowmy_0-1702124033994.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.