Forum Discussion

sjpbi's avatar
sjpbi
Frequent Visitor
1 year ago

How to calculate 12month rolling average

How to calculate 12month rolling average data 

- I have date table with date, month column (yyyy-mm)

-I have a main table with the data of ID, hiredate, sepdate, empname

-I need to calculate rolling average of my actual headcount (12months period) and so on

 

Data Modeling
Reports

9 Replies

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    1 year ago

    You can iterate the average over applicable months with AVERAGEX. In my DAX below, I'm assuming:

    • "table with date, month column (yyyy-mm)" is called Dates
    • "main table with the data of ID, hiredate, sepdate, empname" is called Data
    • if someone was an employee for even a single day within the month, they should count towards headcount (extremes would be they are hired on last day of month or separate on first day of month)
    • there is an active relationship between Dates and Data (so will use REMOVEFILTERS to ignore this)
    Rolling 12 Month Avg Headcount = 
VAR _thisMonthAndLast11 = 
    CALCULATETABLE( 
        VALUES( Dates[month] ), 
        DATESINPERIOD( Dates[Date], MAX( Dates[Date] ), -1, Year ) 
    )
RETURN
AVERAGEX( 
    _thisMonthAndLast11, 
    VAR _thisMonthEnd = CALCULATE( MAX( Dates[Date] ) ) 
    VAR _thisMonthStart = CALCULATE( MIN( Dates[Date] ) ) 
    RETURN 
    CALCULATE( 
        COUNTROWS( Data ), 
        Data[hiredate] <= _thisMonthEnd, 
        Data[sepdate] >= _thisMonthStart, 
        REMOVEFILTERS( Dates ) //ignore active relationship
    ) 
)

     

    • freginier's avatar
      freginier
      Icon for Solution Sage rankSolution Sage
      1 year ago

      Hey there!

       

      To calculate a 12-month rolling average for actual headcount in Power BI using DAX, you can create a measure that dynamically averages the last 12 months of data.

      Use the following DAX formula to calculate the 12-month rolling average headcount:

      Rolling_12M_Avg_Headcount =
      VAR CurrentMonth = MAX('DateTable'[Month])
      RETURN
      AVERAGEX(
      DATESINPERIOD(
      'DateTable'[Month],
      CurrentMonth,
      -12,
      MONTH
      ),
      CALCULATE(SUM('HeadcountTable'[Actual Headcount]))
      )

       

      Hope this helps!

      😁😁

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi sjpbi 

     

    Since you have dates table with a date column, use that one to simplify time intelligence calculations. Here's  sample measure:

    Avg Amount R12M = 
VAR _period =
    DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -12, MONTH )
VAR _result =
    CALCULATE (
        AVERAGEX ( VALUES ( Dates[Month] ), [Sum of Amount] ),
        _period,
        REMOVEFILTERS ( Dates )
    )
RETURN
    _result

    REMOVEFILTERS ( Dates ) is neccessary is if you haven't marked your dates table as such. Otherwise, it is not needed.

    Please see the attached sample pbix.

     

    • sjpbi's avatar
      sjpbi
      Frequent Visitor
      1 year ago

      How to filter this to only get exactly 12 months ago data moving that when it has a new month it will just adjust accourdingly?  since i have a lot months years almost 2 decades in my dataset and is was summing all up

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi sjpbi,

        If you want to control the above formula calculation range, you can add an if statement outer of the result to compare current row context date with last date in table if it is included in the last 12 months.

        Regards,

        Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi sjpbi ,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng