Forum Discussion

HollyPludeTFA's avatar
HollyPludeTFA
Frequent Visitor
3 years ago

DAX Code for Previous Year Rolling Averages

I have read numerous posts on issues surround Rolling Averages, but I have not found one that answers my specific problem.

 

I have a dashboard for the 2022 Fiscal Year. I need to write a formula that creates a 4 month (and then a separate formula for a 12 month) rolling average, but for the SPLY in 2022. I have used PREVIOUSYEAR, DATESBETWEEN, SAMEPERIODLASTYEAR all to no avail. Either these are the wrong expressions or I am really not understanding how to write this code.

 

This is my current code. It works exactly as it should for the current year, but even when utilizing date filters on the page, correct results are not returned.

 

Starts units R4M =
VAR NumOfMonths = 4
VAR LastCurrentDate =
    MAX ( Dates[Date] )
VAR Period =
    DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( Dates[Month] ),
            'Key Measures'[Frame]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, Dates[Date])
VAR LastDateWithSales = MAX ( SCHEDULE_SACTIVITY[SCH_START_DATE])
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )
 
This is the code for the 'Key Measures' [Frame] portion of the code:
 
Frame =
CALCULATE(COUNT(SCHEDULE_SACTIVITY[SACTIVITY_ID]),
USERELATIONSHIP(Dates[Date],SCHEDULE_SACTIVITY[ACTUAL_START_DATE]),
SCHEDULE_SACTIVITY[SACTIVITY_ID] = 82
)
 
Thank you in advance for any assistance you might be able to lend me.
Need Help

10 Replies