Forum Discussion

nrenaud's avatar
nrenaud
Helper I
6 years ago
Solved

Percent Change between 2 columns on a Matrix

I am trying to create a measure (or possibly multiple measures) that will show the percent change between 2 years, for multiple sets of data. Is this something that is possible with DAX? The problem I am running into is making this possible for different types of data (shown below) and also displaying it as a column between each year.

Ideally, I would want a percent change column between each year. Obviously this is possible with excel, but I do not know that there is a way to be able to display this in Power BI.

Also, maybe this shouldn't be done as a measure? I'm really unsure at this point.

 

Thanks!

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi nrenaud ,

     

    Please kindly check the attachment whether help.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • nandic's avatar
    nandic
    Resident Rockstar
    6 years ago

    nrenaud ,
    Here is formula for calculating year over year percentage change:

    YoY Difference % =
    var _currentYear = [Total Sales]
    var _previousYear = CALCULATE([Total Sales],PREVIOUSYEAR('Date'[Date]))
    RETURN
    DIVIDE(_currentYear,_previousYear)

    Format this measure as percentage.


    In order to make this work you need to mark your date table as date table. This will enable you to create all sort of time intelligence functions (previous day, month, year, year to date, month to date....).


  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi nrenaud ,

     

    How do you create the Matrix visual? Are the fields marked with a red box from many measures or just one column?

    It's best to create a dummy .pbix file, removing sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • nrenaud's avatar
      nrenaud
      Helper I
      6 years ago

      Icey , the fields are a mix of measures and from 1 column in a table.

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi nrenaud ,

         

        It's best to create a dummy .pbix file, removing sensitive information, for test. It is suggested to upload your file to OneDrive for Business and then paste the link here.

         

         

        Best Regards,

        Icey