YOY qty Measure, Dynamic

[sneevand]

Hi Guys,

We have below data

yr      qty   salamt

2021 100  20000

2020 2     200

2019 3      99

We would like to get AVG price yoy

2021 =20000/100= 200

2020 =200/2= 100

2019 =99/3= 33

We achieved above by simple dax

CY=calculate(salamt/qty,dateadd(dimdate,1,Year)) 

PY =dateadd(dimdate,1,Year) replaced 1 with -1

PY-1=dateadd(dimdate,1,Year) replaced 1 with -2

Change Per Year= cy-py/qty py

dynamically for all years

but what if have to do it for 20 yrs [By above method i end up creating 20 measures], i dont want to create 20 measures, Can i pass middle value as dynamic paramter based on year filter? or Some Dummy table?

Regards,

Sneevand

[V-lianl-msft]

Hi @sneevand ,

Please refer to these thread:

https://community.powerbi.com/t5/Desktop/YoY-Calculations/m-p/342299 

https://community.powerbi.com/t5/Desktop/YoY-Qty-Measure/m-p/1604993 

Then use the year slicer to dynamically display the comparison with the previous year.

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please remove any sensitive data before uploading.

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

[parry2k]

@sneevand not sure why you will do that, you just need to create 3 measure and you should use date dimension and then use previousyear dax function to get value for the previous year, which means whatever year you are looking at, you will get previous year value.

There are tons of posts on time intelligence and previous year calculations.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡