Comparing chnages between a base year and current year

[mnirayo]

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]

Thank 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

[Anonymous]

@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,
Lydia

[mnirayo]

@Anonymous 

Thank you for your time and support. But I do not know why I am getting an infinity as a final result. 

regards,

Mogos

[Anonymous · Accepted solution]

@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

[Anonymous]

@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,
Lydia