cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Need to Compare Current Month with Last 3 Months Average Data

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

 

Capture.PNG

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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
    )
)

Need_to_Compare_Current_Month_with_Last_3_Months_Average_Data

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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
    )
)

Need_to_Compare_Current_Month_with_Last_3_Months_Average_Data

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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.

 

 

Hi @Maha1991,

 

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

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 🙂 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors