Forum Discussion
Grouping dates in a matrix
- 4 years ago
See if this works for you. You need to create a table which has the layout you need. In my example I have a date table, so I am creating the Matrix Layout table referencing the Date Table and adding the Prior and After rows as follows:
Matrix Layout = VAR _Current = CALCULATETABLE ( SUMMARIZE ( 'Date Table', 'Date Table'[Year], 'Date Table'[Month], 'Date Table'[MonthNum] ), 'Date Table'[Year] = YEAR ( TODAY () ) ) VAR _otherPeriods = { ( "Prior to", YEAR ( TODAY () ), 0 ), ( "After", YEAR ( TODAY () ), 13 ) } RETURN UNION ( _Current, _otherPeriods )Once the table is loaded, I'm adding a sorting order for the "Year" column using:
Sort = SWITCH( 'Matrix Layout'[MonthNum], 0,1, 13, 3, 2)To get:
The model is as follows:
Sort the "Year" column by "Sort"; Sort the "Month" column by "MonthNum" column.
Create the following measure (I'm using a simple SUM for the calculations, so use whatever you need)
Final Measure = VAR _Prior = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] < YEAR ( TODAY () ) ) ) VAR _After = CALCULATE ( [Sum Sales], FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] > YEAR ( TODAY () ) ) ) VAR _Current = CALCULATE ( [Sum Sales], TREATAS ( VALUES ( 'Matrix Layout'[Month] ), 'Date Table'[Month] ), FILTER ( 'Date Table', 'Date Table'[Year] = YEAR ( TODAY () ) ) ) RETURN SWITCH ( SELECTEDVALUE ( 'Matrix Layout'[MonthNum] ), 0, _Prior, 13, _After, _Current )Now create the matrix visual with the fields from the Matrix Layout table, whatever you need as rows and the [Final measure] to get:
I've attached the sample PBIX file
Hi Paul!
Thank you so very much, It worked!
One small thing though, I couldn't sort the month name by month number. Cause the value "2022" is repeated twice in column Month name. I get this error:
This is what the final table looks like:
How did you get your to sort?
You know what I just changed things up a little to make it work:
Final matrix:
Thanks a lot