How to calculate YOY 3 month moving average?

[KJChin]

Hi, 

I am working with DAX measure. I am trying to find a way to create a YOY of the same period last year. I need to compare current month (or any month in the chart), with the average of 3 months value from previous year. Eg, if current month is Jan 2025, then we should have average value from Nov 2023, Dec 2023 and Jan 2024. Then we calculate the YOY. 

Below is my current measure.

Revenue PY = 
   CALCULATE(
       [Total Revenue in USD],
       SAMEPERIODLASTYEAR(DimCalendar[Date])
   )

Revenue YOY % = 
DIVIDE(
        [Total Revenue in USD] - [Revenue PY], 
        [Revenue PY]
    )

 I tried to use "-3" for the Revenue PY, to test the outcome, yet it does not come out as desired. I don't know if this is possible in DAX or other way in Power BI?  

Any help is much appreciated. Thank you.

[Anonymous]

Hi,@KJChin .It's great to see that you solved your problem and that you shared the method to the forum,
you can mark your option as a solution which will help other users in the forum.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[KJChin · Accepted solution]

I found a solution. It will take one more measure. I need to first keep my Revenue PY. Then use below measure. DATESINPERIOD does help. 

Past Year 3 Months Average = 
DIVIDE(
    CALCULATE(
        [Revenue PY],
        DATESINPERIOD(DimCalendar[Date], MAX(DimCalendar[Date]), -3, MONTH)
    ),
    3
)

 Thanks for the helps, everyone.

[johnt75]

Because you need the average you need to build a table containing the months you want to calculate over and then you can calculate the revenue for each month. Something like

Revenue PY =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DateStart =
    EOMONTH ( MaxDate, -15 ) + 1
VAR DateEnd =
    EOMONTH ( MaxDate, -12 )
VAR DatesToUse =
    CALCULATETABLE (
        VALUES ( 'Date'[Year Month] ),
        DATESBETWEEN ( 'Date'[Date], DateStart, DateEnd )
    )
VAR Result =
    AVERAGEX ( DatesToUse, [Total Revenue in USD] )
RETURN
    Result

[KJChin]

Thanks @johnt75 I tried the method, somehow I get back the same revenue as in [Total Revenue in USD] for each month. 

Below is something I wish to calculate, as in [Past year 3 months average column]

[bhanu_gautam]

@KJChin , Try using

DAX
Average Revenue PY 3 Months =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
DimCalendar[Date],
DATEADD(DimCalendar[Date], -1, YEAR),
-3,
MONTH
),
[Total Revenue in USD]
)
)

Revenue YOY % =
DIVIDE(
[Total Revenue in USD] - [Average Revenue PY 3 Months],
[Average Revenue PY 3 Months]
)

[KJChin]

Thanks @bhanu_gautam 

By right, the DAX should have work since it consider the previous one year and take 3 months in consider. Yet I get error in this. 

Have you ever encounter this before? My [Total Revenue in USD] is in measure. While date is in a table.