Forum Discussion
Comparing chnages between a base year and current year
Dear Community,
I am able to compare the change in value between the current year and last year with the help of time intelligence DAX formulas. But, additionally, I want to compare the change in values between the current year and the base year. In my dataset, I do have a date column, where 2010 is my base year and I want to compare the change in 2019 compared to 2010. Can you help me with how I can do that?
Thank you,
Mogos
mnirayo ,
Have you checked the PBIX file I attached? There are some issue with your data when you get infinity . Check the example below.
You can change the measure to the following:Measure = Divide(CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Attribute]=2018)),CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Attribute]=2010)),0)-1
Regards,
Lydia
6 Replies
@mnirayo ,
Please share sample data of your table following the guide in the blog below and post exepected result based on sample data.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
LydiaThank you, Lydia, for your time.
Here is my sample data
And I need to calculate the amount of change in 2018 compared to 2010. Basically, I need [(2018/2010)-1]*100 using dax. I thank you in advance for your time.Regards,
Mogos
mnirayo ,
Unpivot your table in Power BI Desktop query editor.
Then create the following measure, and set it to be percentage. For more details, please check attached PBIX file.Measure = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Attribute]=2018))/CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Attribute]=2010))-1
Regards,
LydiaAnonymous
Thank you for your time and support. But I do not know why I am getting an infinity as a final result.
regards,
Mogos
