Forum Discussion

mnirayo's avatar
mnirayo
Regular Visitor
7 years ago
Solved

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...
Data Modeling
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
mnirayo
Regular Visitor
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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