Average last 3 month

[Harry_Tran]

Hi all.

I need to calculate the average of 'Total Sale" for the last 3 months.

My measure:

    AVG_Last_3_Month = CALCULATE ([Total Sale],

                                               DATESINPERIOD ('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH))/3

Is there anyway that for the first month it would be devided by 1, the second month devided by 2, then after the third month it would be divided by 3.

Thank you so much.

[parry2k · Accepted solution]

@Harry_Tran try this measure

$ Rolling Avg 3 Months Tran Months = 
//based on number of transaction months
VAR __filterDate = DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )   
VAR __numberofMonths = 
    CALCULATE (
        CALCULATE ( COUNTROWS (  VALUES ( 'Date'[Month] )  ), YourTable ),
        __filterDate
    )

RETURN
DIVIDE (
    CALCULATE (
         [Total Sale],
         __filterDate
    ),
    __numberofMonths
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Harry_Tran]

 @parry2k ,

Thank you so much, it works.