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,
So the current year will always be the one broken down into months. So by the next year 2023 would be broken down and 2022 will be aggregated with the previous years (2021, 2020, etc).
Thanks for trying to help out
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
- Anonymous4 years agoNot applicable
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?
- Anonymous4 years agoNot applicable
You know what I just changed things up a little to make it work:
Final matrix:
Thanks a lot
- PaulDBrown4 years agoCommunity Champion
Of course...you're right. It cannot sort beacuase there are two rows with 2022. This may actually be a bug. Let me explain. Originally I built the Matrix Layout with both the "Prior to 2022" and "after 2022" in the Month column. I did the sorting to see the visual. I then decided to move the "pior to" and "After " texts to the Year column, That's probably why it is still sorting the months correctly.
Anyway, an easy fix. Use this codefor the Matrix Layout table and you should be able to sort the months correctly (you also need the "sort" column for the years)
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 )- Anonymous4 years agoNot applicable
That explains it. Thanks again for your support!