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

mnirayo's avatar
mnirayo
Regular Visitor
7 years ago

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'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