Forum Discussion
DAX
I have a table with different customers,products,total sales (amount and revenue) for the years 2017 and 2018.
I made a DAX formula to calculate the average prive.
Now i want to calculate the difference between the price from 2018 with the price from 2017.
How does such DAX formula look like?
Kind regards
Hi marsclone,
I had a test to calculate the difference based on the data as your descripted. Please check the following steps:
1.Enter the data and new a Year column using the formula:
Year = YEAR(Table2[date])
2.Create three measures to calculate that you want.
ave 2017 = CALCULATE(SUM(Table2[Revenue])/SUM(Table2[qty]), FILTER(Table2,Table2[Year]=2017))
ave 2018 = CALCULATE(SUM(Table2[Revenue])/SUM(Table2[qty]), FILTER(Table2,Table2[Year]=2018))
difference = [ave 2018]-[ave 2017]
3.Then we can get the result as below.
For more information, please check the pbix as attached.
https://www.dropbox.com/s/rfdinx3zzxg9i5h/DAx1.pbix?dl=0
If the above DAX don’t help, please share sample data of your table and post expected result.Regards,
Frank
3 Replies
- Hello
I have a table with different customers,products,total sales (amount and revenue) for the years 2017 and 2018.
I made a DAX formula to calculate the average prive.
Now i want to calculate the difference between the price from 2018 with the price from 2017.
How does such DAX formula look like?
Kind regards - Check out my Time Intelligence The Hard Way measure in the Quick Measure Gallery.
Hi marsclone,
I had a test to calculate the difference based on the data as your descripted. Please check the following steps:
1.Enter the data and new a Year column using the formula:
Year = YEAR(Table2[date])
2.Create three measures to calculate that you want.
ave 2017 = CALCULATE(SUM(Table2[Revenue])/SUM(Table2[qty]), FILTER(Table2,Table2[Year]=2017))
ave 2018 = CALCULATE(SUM(Table2[Revenue])/SUM(Table2[qty]), FILTER(Table2,Table2[Year]=2018))
difference = [ave 2018]-[ave 2017]
3.Then we can get the result as below.
For more information, please check the pbix as attached.
https://www.dropbox.com/s/rfdinx3zzxg9i5h/DAx1.pbix?dl=0
If the above DAX don’t help, please share sample data of your table and post expected result.Regards,
Frank
