Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Year on Year Change

Hi all, 

 

I am trying to display the variation/change from, for example, 2022 to 2023 within a matrix visual on Power BI. To do so, I have created the quick measure helping to calculate that. But, when it comes to the actual visualisation of the data, it is kind of confusing to see because columns are not really nicely displayed. How can we have a third column allowing us to have the change year on year and whilst still having the yearly data of the two years and still being easyly understandable?

 

Thank you for your reply,

Best regards.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Based on your description, I created this data.

     

    Please follow these steps:

    1.Create measure to show data for the same period last year.

    Last year's data =

VAR pre = DATE(YEAR(MAX('Table'[date])-1),MONTH(MAX('Table'[date])),DAY(MAX('Table'[date])))

VAR pre_value = CALCULATE(MAX('Table'[value]),FILTER(ALLSELECTED('Table'[date]),'Table'[date] = pre))

RETURN

pre_value

    2.Create measure and calculate year-on-year growth rates.

    year-on-year growth rate =

DIVIDE((MAX('Table'[value])-'Table'[Last year's data]),'Table'[Last year's data])

    3.The settings of the fields in the matrix and the final effect is shown in the figure below.

     

    If this result is not what you expect, please provide the relevant pbix file or effect diagram to better help you solve the problem.

     

    Best Regards,

    Clara Gong

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      Thank you for your response. However, I can not make this work. Indeed, the issue seems to be the fact that my values come from a measure (whereas you have a column): I have a table composed of several excel documents (my datasource is a file so my 2022 data is followed by my 2023 data in my table) and in order to display my data, I used a measure to have it all in a matrix visual. To give you more info, I used a measure starting like that: MeasureForValues = SWITCH(SELECTEDVALUE('Table1'[Column1]), Text, [Measure1],...)

      And, what I want to display is a matrix that has:

      - rows: the Text refered to in the measure;

      - column: my date table (I want to have a yearly split so my years: 2022 vs 2023);

      - values: my MeasureForValues measure;

      - AND: I want a column, next to the one for 2023, having the year-on-year growth for those two periods.

       

      Any ideas about a similar measure/way to proceed in order to have the year-on-year growth with values coming from a measure in the first place?

       

      Thank you!

      Best regards.