Average and percentage of change for each month 2nd!

[Anonymous]

Hi, 

I have asked help to find average and percentage of change in a post for a sample data and lbendlin helped me to solve the problem. However, when I tried to implement the answer to find average of last three years (same month) in an actual probelm I couldn't reach to the expected result. Would you mind give me some tips? 

Here is the original post:  

https://community.powerbi.com/t5/Desktop/Average-and-percentage-of-change-for-each-month-Revised-and...

Here are the PBI, Excel files for sample data:  https://www.dropbox.com/sh/notqpq6nuvr779o/AABKz8BVY64DprrjrbamVC68a?dl=0 

As you see below now I used exactly the same formulas, but I failed to make it work!!! Do you what I am where I need to revise the formula? 

Avg_3LY_Same_Mth_Value =

var s=year(SELECTEDVALUE(Merge1[Date]))

var m=month(SELECTEDVALUE(Merge1[Date]))

return AVERAGEX(FILTER(ALL(Merge1),month(Merge1[Date]) =m && year(Merge1[Date]) in {s,s-1,s-2}),Merge1[Value])

Thanks a lot for your help! 

[v-kelly-msft]

Hi @Anonymous ,

The sample .pbix file you provided doesnt contain the information about the visuals for value and average,could you pls modify it for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft 

Sure, I did! I added some titles to make it clear. 

[v-kelly-msft]

Hi  @Anonymous ,

After checking your .pbix file,I'm a little confused about your request.

For the dax expressions,they are all fine,and in my opinion,the results are also right.

As you have applied a filter in the year which is greater or equal to 2021,so the results are showing the values for January and February and it has impacted all the visuals,because they have interactions between.

Take Jan 1,2021 for example,the last 3 years,same month should be 2021.1,2020,1,2019.1,as show below:

And the average value is 27167,as show in the matrix:

So what is the problem?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft thanks to get some time on this. There is actually - as you said - no problem with the sample data. 

But when I apply exactly the same formula in my real assignment it work differently why i got really confused: 

Avg_3LY_Same_Mth_Value =

var s=year(SELECTEDVALUE(Merge1[Date]))

var m=month(SELECTEDVALUE(Merge1[Date]))

return AVERAGEX(FILTER(ALL(Merge1),month(Merge1[Date]) =m && year(Merge1[Date]) in {s,s-1,s-2}),Merge1[Value])

(43132+26283+32971)/3=34129 not 61860 (it seems the averagex counting some other values as well!). Really have got confused! 

[v-kelly-msft]

Hi @Anonymous ,

Cant tell why,it seems OK,while testing the sample data,all the results are right:

Take below steps for workaround:

If you clear the selection in slicer,will the matrix returns correct values?

If not,try below measure :

Avg_3LY_Same_Mth = 
var s=SELECTEDVALUE(data[Date].[Year])
var m=SELECTEDVALUE(data[Date].[Month])
return AVERAGEX(FILTER(ALL(data),'data'[Date].[Month] =m && 'data'[Date].[Year] in {s,s-1,s-2}),data[Value])

If it returns correct values,it should be a format error.

Let me know once you get a feedback.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , You can try measure like these examples

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling 2 before 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-2),-2,MONTH))

Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-3),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.