Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone!
I am working on a matrix viz where in columns I have my date. I am trying to get a view where years 2020-2023 will be collapsed to Quarter level (2020 Q1, 2020 Q2, 2020 Q3, ... , 2023 Q4) but 2024 has to be shown in monthly view.
The date that I am using has date data type and I have the year-quarter-month hierarchy. Under the viz I have some buttons for drilling up/down, but they apply to the whole viz.
Have You encountered such problems, is it even solvable?
Thanks in advance
Hi, @NarekGrigoryan
I am glad to help you.
Because you didn't give a dataset for testing, I created a simple dataset myself:
Then create a date table that contains all the dates for the Sales table:
DateTable =
ADDCOLUMNS(
CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])),
"Year", YEAR([Date]),
"MonthNumber", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT(QUARTER([Date]), "0"),
"YearQuarter", FORMAT([Date], "YYYY") & "-Q" & FORMAT(QUARTER([Date]), "0"),
"YearMonth", FORMAT([Date], "YYYY") & "-" & FORMAT([Date], "MM")
)
Create a calculated column to distinguish between the 2020-2023 display quarter and the 2024 display month:
DisplayDate =
IF(
DateTable[Year] <= 2023,
DateTable[YearQuarter],
DateTable[YearMonth]
)
Make sure that the relationship between the Sales table and the date table is established through the Date field:
Drag Product to Rows of the matrix, Newly created calculated columns to Columns of the matrix, and SalesAmount to Values:
I have attached the PBIX file of this simple example below, I hope it will be helpful to you.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Thanks for the solution provided.
It will surely work only what I also want to have is the possibility for users to expand/collapse the view.
So for example if the user wants to see 2023-Q4 in details he can expand it and see the monthly view.
I can see that for rows there are +/- buttons and user can manually open what they want. But it seems that the same doesn't work for coulmns 😞
Hi, @NarekGrigoryan
Perhaps you could try dragging the Date directly to the Rows?
But then the date hierarchy would be the same for all years:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
77 | |
76 | |
45 | |
31 | |
27 |
User | Count |
---|---|
98 | |
89 | |
52 | |
48 | |
46 |