Need to Compare Current Month with Last 3 Months Average Data

[Maha1991]

Hi , I need a help or idea from you. I have a data with Dept name and its corresponding Amount for each Dept for each Month like below :

Dept name Amount Period

XXX 20 Jan,2018

XXX 30 Feb,2018

XXX 50 Mar,2018XXX 70 April,2018

....

....

...

YYYY 20 Jan,2018

YYYY 30 Feb,2018

YYYY 50 Mar,2018

YYYY 70 April,2018

....

....

...

I need to calculate the Average of Last 3 months (Ex. For April, I need to calculate the average Amount of (Jan,Feb,Mar) and Compare the same with current month. I need to add another column as Dropped? = If AVG (Last 3 Months) > Current Month = YES else NO. And also If I choose the Particular month in slicer I need to get those Month, Amount, Last 3 months average and Dropped YES/NO alone in my Report.

Could you please let me know how to do this.

I've created a calculated column for 3month Average as below

AVG3mth = CALCULATE(SUM('REX Drop'[Amount]),DATESINPERIOD(Calender[Date],LASTDATE('REX Drop'[Period]),-3,MONTH))/3

But it just dividing the current month by 3. Please help. Attached my current report screenshot

[v-jiascu-msft · Accepted solution]

Hi @Maha1991,

The period starts from the last day of last month. Please refer to my test below.

avg3mth =
CALCULATE (
    AVERAGE ( MonthlySales[Sales] ),
    DATESINPERIOD (
        'DimDate'[Datekey],
        EOMONTH ( MIN ( 'DimDate'[Datekey] ), -1 ), //the last day of last month.
        -3,
        MONTH
    )
)

Best Regards,
Dale

[Maha1991]

It still doesn't works.

I have created a Calender Table Linked the Date column of Calender Table with Date column of my Table.

Used the DAX that you have mentioned.

But it still shows error.

[v-jiascu-msft]

Hi @Maha1991,

What's the error message? Can you share the file, please?

Best Regards,
Dale

[Maha1991]

Hi Dale,

Apologies. It works for your DAX formula. I think there was a Sync issue before and now works perfectly. Thank you very much 🙂