topic Calculate Absolute Change Y-o-Y in DAX Commands and Tips

Calculate Absolute Change Y-o-Y

Dlahey — Wed, 22 Dec 2021 14:36:05 GMT

Hi All,

I am sure this is simple - I cannot seem to figure it out.

I have created a calculation to divide Dollars by Units to give me an Average Selling Price.

How can I create a formula to calculate the absolute change between my calculation for Y-o-Y.

For Ex: 2021 Average Selling Price = $335 and 2020 = $407 the result should show an absolute change Y-o-Y of -$72.

How can I build a DAX query to calculate this?

Thanks very much,

Dave

Re: Calculate Absolute Change Y-o-Y

amitchandak — Wed, 22 Dec 2021 16:22:21 GMT

Dlahey , Assume you have Average Selling Price

YTD Sales = CALCULATE([Average Selling Price],DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE([Average Selling Price],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Re: Calculate Absolute Change Y-o-Y

Dlahey — Wed, 22 Dec 2021 17:41:01 GMT

thank you - I have tried this and it doesn't seem to give me the desired output

ASP Consolidated LYTD = calculate('Calculated Table'[Average Selling Price - Consolidated],DATESYTD(dateadd(Working_Days[Reference Date].[Date],-1,Year),"12/31"))

ASP Consolidated YTD = calculate('Calculated Table'[Average Selling Price - Consolidated],datesytd(Working_Days[Reference Date].[Date],"12/31"))

the output shows as -$335 instead of -$72

Re: Calculate Absolute Change Y-o-Y

v-kkf-msft — Mon, 27 Dec 2021 08:51:50 GMT

Hi Dlahey ,

Has the following measure without using the time intelligence functions been tried? Does it return the correct value? If it is still wrong, please provide your sample data.

Measure = var _YTD = CALCULATE( [Average Selling Price - Consolidated], FILTER( ALLSELECTED( Working_Days ), year( Working_Days[Reference Date] ) = YEAR( TODAY() ) ) ) var LYTD = CALCULATE( [Average Selling Price - Consolidated], FILTER( ALLSELECTED( Working_Days ), year( Working_Days[Reference Date] ) = YEAR( TODAY() ) - 1 ) ) return _YTD - LYTD

Best Regards,
Winniz

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