Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 int...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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's avatar
parry2k
Super User
7 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

parry2k Worked a treat!  Thanks for the clear explanantion!