12M Rolling Metrics - DAX code - How to exclude the first 11 months

[quyenduong]

Dear community,

I have the following is DAX code in Power BI to calculate the Rolling 12 Attrition Rate = (sum of leavers in the last 12 months) / Average Headcount in the last month and it works properly:

Rolling 12M Attrition Rate =

VAR __NrOfRollingMonths = 12

VAR __CurrentDate = MAX ( 'Date Table'[Date] )

VAR __RollingMonths =

       FILTER (

            ALL ( 'Date Table' ), 'Date Table'[Date] > EDATE ( __CurrentDate, - __NrOfRollingMonths )

                 && 'Date Table'[Date] <= __CurrentDate

)

RETURN

       CALCULATE ( DIVIDE([Voluntary Leavers], AVERAGEX(VALUES('Date Table'[Report Month]),[Headcount Permanent Contract])), __RollingMonths )

However, for the first 11 months, it give very small % of Attrition because it doesnt have data of previous 12M (see red collumn).

(these numbers in the screenshot are modified and fictional, the screenshot is just to show the ideal outcome)

From the Date column from Date Table, how can we edit the DAX above to achieve the green column that:

- It calculates exactly the same logic

- Based on the column Date from Date Table, can you please give Attrition Rate blank for the first 11 months of the data, (the Date column from Date Table is everyday from January 2021 till March 2023) so I want from Jan 2021 till November 2021, the Attrition rate is blank

I tried several ways to edit this code but it doesnt work.  Thank you in advance for your answer. 

[Anonymous]

HI @quyenduong,

You can add a variable to get the first date from current table records with filter effects, then you can package expression with if statement and use this variable as condition to compare with current axis date and skip the calculations if current date is included in the first 11 months.

Regards,

Xiaoxin Sheng