Need to compare current month with average of last 3 months in power bi

Question

I have a data with Dept name and its corresponding Amount for each Dept for each Month like below :

Table1 :

Dept name Amount Period

 XXX        20     Jan,2018
 XXX        30     Feb,2018
 XXX        50     Mar,2018
 XXX        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 Dept XXXX, If I select April Month, It needs to calculate the average Amount of (Jan,Feb,Mar)(20+30+50)/3 =33.33) and Compare the same with current (April) month (70)

I've created a calculated column for Last 3month Average as below (I have also created a Calender Table in Power BI)

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

(But it just dividing the current month by 3 and not the Last 3 Mnths.)

and when comparing If the Average of Last 3 months greater than current month I should highlight it as "YES" since the Amount is dropped when comparing to last 3 months. I have added another column as "Dropped?" for the same.

Dropped? = IF(VALUES('Table1'[Amount])<[AVG3mth], "Yes", "No")

And also If I choose the Particular month (Period) in slicer I need to get those Month, Amount, Last 3 months average and Dropped YES/NO alone in my Report.

Attached my current report screenshot (You will get clear idea if you look into this)

Report Screenshot

Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve — Dragonthoughts

Siddharth Thanga Mariappan Siddharth Thanga Mariappan · Accepted Answer · 2018-09-27T13:31:04

To do this, you will need 1 Calculated Column and 3 Measures.

First, I created a new column called as MonthDiff (Calculated Column)

MonthDiff = DATEDIFF(MIN(Table1[Period]),Table1[Period],MONTH)

So afterwards, I created the Average for last 3 months Measure

Average Last 3 Months = 

   Var selectedmonth = SELECTEDVALUE(Table1[MonthDiff])

   Var startingMonth = (selectedmonth - 4)   

   Var selecteddepartment = SELECTEDVALUE(Table1[Dept name])                                                                                                                                                                        

Return  CALCULATE(AVERAGE(Table1[Amount]), FILTER(ALL(Table1), Table1[MonthDiff] > startingMonth && Table1[MonthDiff] < selectedmonth),FILTER(ALL(Table1),Table1[Dept name] = selecteddepartment))

So, then you can create the current selected value Measure

SelectedAmount = SELECTEDVALUE(Table1[Amount])

Then you can create the drop Measure

Drop = var currentvalue = SELECTEDVALUE(Table1[Amount])   

  Var selectedmonth = SELECTEDVALUE(Table1[MonthDiff])                                                                                                                                                              

  Var startingMonth = (selectedmonth - 4)  

  Var selectedDepartment = SELECTEDVALUE(Table1[Dept name])                                                                                                                                                                         

  Var averagevalue = CALCULATE(AVERAGE(Table1[Amount]), FILTER(ALL(Table1), Table1[MonthDiff] > startingMonth && Table1[MonthDiff] < selectedmonth), FILTER(All(Table1),Table1[Dept name] = selectedDepartment))                                                                    

Return if(averagevalue > currentvalue, "Yes", "No")

This is my final output,

Do let me know, if this helps or not.

My Best Practice

When you are dealing with Measures that involves multiple filters, it's best to declare them using Var and test it by returning the output on the card visual as you develop the measure.

Need to compare current month with average of last 3 months in power bi

1 Answers

My Best Practice