Matrix report

[Anonymous]

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.

 

 

[Anonymous]

@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.

[Greg_Deckler]

@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.

[Greg_Deckler]

@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.

[Anonymous]

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.

[Greg_Deckler]

@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. 

[Anonymous · Accepted solution]

It there a way to integrate that in the existing calander table