Forum Discussion
Matrix report
I am trying to create a report which shows the data of the pervious year data from the selected year in a slicer in a yearly format & the selected year data slicer in months..
For example: I select the Fiscal year 22-23 in a slicer. I should see the FY 20-21, 21-22 as year total. But the FY 22-23 in months format. I also need the months header column to be fixed the complete 12 months. For the visual width to remian fixed. I had that in the excel report see the screenshot below.
6 Replies
Anonymous (1) The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community
If that doesn't help, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hello Greg_Deckler ,
The suggestion that you gave will work, I might have to make few changes. I just wanted to see how to show all the 12 month by default. As if we select the pervious fiscal year. I am able to see all the month but when the current fiscal year is selected it will only show the month that have passed. I need that to remain static as I need the visual to remain in the same size. If you see the below screenshot. Which shows the data of the pervious 3 fiscal years. And the Last fiscal year is spread out in months.
If there is any suggestion regarding that.
Anonymous If I'm understanding what you want correctly, I think that is just a bridge too far for the matrix visual to have columns kind of reorder themselves like that. Maybe, again, if you constructed your disconnected table and measure just right, maybe. I an thinking three columns in your disconnected table.
Category Sort Year APR 1 2021 MAY 2 2021 JUN 3 2021 FY 21 4 2021 JUL 5 2022 AUG 6 2022 SEP 7 2022 OCT 8 2022 NOV 9 2022 DEC 10 2022 JAN 11 2022 FEB 12 2022 MAR 13 2022 ARP 14 2022 MAY 15 2022 JUN 16 2022 FY 22 17 2022 So basically you could contruct your measure that based on the slicer selection, let's say 2022, it only shows the months for 2022 and it shows all of the FY. If they select 2021 then it shows the 2021 months, etc.
Greg_Deckler That is what i was actully trying to achive but need that to be a little dynamic like if i select a different year in a slicer it should use that and change the LY and PY accordingly to that.
Anonymous Might be possible if you construct your measure just right. Put all of your years and months into a disconnected table and construct a measure so that it only returns values based on certain conditions. I mocked up a really simple example. PBIX attached below signature. So, the only columns that get displayed are the ones where the measure returns a value. With the proper logic you should be able to get it to do what you want.
