Forum Discussion
DAX Calendar - Grouping
Hi,
I am using the DAX calendar function as below:
CALENDAR (
DATE ( 2019, 1, 1 ),
DATE ( 2019, 12, 31 )
)and this is working exactly as hoped as I can use it to total my data into months for the year.
However I have records that are pre 01/01/2019 and I want to show them as pre the calendar start date so I would have something like below:
Is there any additonal code i can add to my calendar to group anything pre 2019?
Any advice appreciated
Anonymous you need to add following columns in your date table
Month = IF( YEAR( Calendar[Date] ) < 2019, "Pre 2019", FORMAT( Calendar[Date], "MMM-YY" ) )
add another column to sort the month
Month Sort = IF( YEAR( Calendar[Date] ) < 2019, "2019-00", FORMAT( Calendar[Date], "YYYY-MM" ) )
Select Month column and goto modelling tab, select sort by column and choose Month Sort
Now on matrix visual, put Month column and your value column on values and you will achieve the result.
2 Replies
Anonymous you need to add following columns in your date table
Month = IF( YEAR( Calendar[Date] ) < 2019, "Pre 2019", FORMAT( Calendar[Date], "MMM-YY" ) )
add another column to sort the month
Month Sort = IF( YEAR( Calendar[Date] ) < 2019, "2019-00", FORMAT( Calendar[Date], "YYYY-MM" ) )
Select Month column and goto modelling tab, select sort by column and choose Month Sort
Now on matrix visual, put Month column and your value column on values and you will achieve the result.
parry2k Worked a treat! Thanks for the clear explanantion!