Forum Discussion
YOY qty Measure, Dynamic
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
2 Replies
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.⚡
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.
