Wanting to create a matrix (or similar visual) that shows values for multiple categories over 3 yrs

[Anonymous]

Hi Everyone!

Hoping to get some assistance please.

Scenario:

I am trying to create a matrix (or similar visual) that shows values of 10 categories, over the same period for the last three years.

(e.g Dec 2021 Dec, 2022, Dec 2023.)

This matrix will be one of several visuals on the page, with a filter visual which allows the user to select the reporting Month Year.

So for example Dec 2023 selection on the filter for the page, should show me December results for all visuals, but the for maxtrix, I want to see Dec 2023, as well as Dec 22 and Dec23.

Here is an example of what I want to end up with:

I've seen videos on using DATEADD to write a measure, however only for one category. I have ten categories, so do I need to write a measure for each of them?? I thought this is probably not the right approach. Am I better off doing somethign in Power Query instead?

And how can I keep Dec 2022 and Dec 2023 values in the matrix, instead of having it disappear when I add a date filter?

I did see a you tube video about creating a realtionship but making it inactive, so its only active when called upon in a measure....is this something that would work?

I'm open to all suggestions - thank you very much! 😊

[danextian]

Hi @Anonymous ,

This can be fairly done in Power BI and you won't need overly complex queries or DAX formulas.  What you'll need is a disconnected Dates table (no relationship to fact) as a regular Dates table  will filter the view of your visual (eg clicking Dec 2023 will show just Dec 2023) and then a measure to filter your visual. As to the measures, you can just move them to rows or you can follow a more complex approach of creating a measure that returns other measures depending on the current row value of the field you put in the row tile. For your use case, I believe, the former would suffice.  Please see sample below and the attached pbix for your reference:

[vicky_]

You can use a Date Table to help - create a column for the month name, and exclude the year from the slicer (so it will show Dec of any year). In your matrix, keep the year and month.
If you don't want your filter to only show the selected year from the slicer, there's no need to create new measures or mess around with the relationships (unless there's some hidden requirements that aren't explained?). instead, you can disable visual interactions between the slicer and matrix: https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=p...

[Anonymous]

Hi Vicky,

Thanks for your reply.

Just to clarify, I still need to use the visual interactions with the date filter and the matrix, as I need it to be dynamic. So if the user selects January 2024 from the drop down, I need the matrix to show the last three years of data. E.g Jan 24, Jan 23 Jan 22. I am at a loss on how to construct things so that the filter only looks at the one column in reference to the interaction, and not exclude the previous two years of data. 🤔