How to calculate 12month rolling average

[sjpbi]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous]

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

[danextian]

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]

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

[v-saisrao-msft]

Hi @sjpbi,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @sjpbi,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

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

[MarkLaf]

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]

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!

😁😁