topic How to group date into 12, 24, 36 months period selection? in DAX Commands and Tips

How to group date into 12, 24, 36 months period selection?

_Dee_ — Tue, 07 May 2024 13:05:19 GMT

Hello,
As per users request, we would like to show dates grouped into the following periods: last 12months, last 24months, last 36months etc and use this as a slicer replacing a standard date slicer where year+month had to be clicked to select the required period.

How to achieve this?

The following DAX is wrong, it only returns if e.g. "Last 24 Months" is chosen the dates between 12 and 24months and not 24months to date. Perhaps there is an easy solution?

Thank you

Date_group =

VAR TotalMonths = DATEDIFF('Table'[Date], TODAY(), MONTH)

RETURN

SWITCH(

TRUE(),

TotalMonths <= 12, "Last 12 Months",

TotalMonths <= 24, "Last 24 Months",

TotalMonths <= 36, "Last 36 Months",

"More than 36 Months"

)

Re: How to group date into 12, 24, 36 months period selection?

Anonymous — Wed, 08 May 2024 01:25:17 GMT

Hi _Dee_

For your question, here is the method I provided:

Here's some dummy data

"Table"

If you are creating a measure, here are the changes I made to your code.

Date_group = var TotalMonths = DATEDIFF(SELECTEDVALUE('Table'[Date]), TODAY(), MONTH) RETURN SWITCH( TRUE(), TotalMonths <= 12, "Last 12 Months", TotalMonths <= 24, "Last 24 Months", TotalMonths <= 36, "Last 36 Months", "More than 36 Months" )

Please note that measure cannot be used in slicer. If you want to filter based on measure, please consider using it in Filters.

Alternatively, you can create a new column. This way, you can use that column in slicer.

Dategroup = var TotalMonths = DATEDIFF('Table'[Date], TODAY(), MONTH) RETURN SWITCH( TRUE(), TotalMonths <= 12, "Last 12 Months", TotalMonths <= 24, "Last 24 Months", TotalMonths <= 36, "Last 36 Months", "More than 36 Months" )

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to group date into 12, 24, 36 months period selection?

_Dee_ — Wed, 08 May 2024 10:01:59 GMT

Thank you Anonymous , indeed the preference would have been to have a calculated column so that it could be used for slicer (easier for users). However that would mean that depending on the context e.g. 01/03/2024 could be part of last 12months or last 24months or last 36months. But I understand in this case this is not possible since each date needs to have a single allocation.
Nonetheless, thank you for your help.