Custom Month Sort Order

[danielbrownau]

I'm trying to create a sort order for months, but where the current month = 1, next month = 2, next next month = 3, etc.

I've tried a couple of measures, but not sure on how to start with this one.

Any help would be greatly appreciated.

[v-lionel-msft · Accepted solution]

Hi @danielbrownau ,

For your issue, you can do like this:

First, create a ‘datediff’ column by ‘DATEDIFF()’ function:

datediff = DATEDIFF(NOW(), Sheet1[Date], MONTH)

Second, create a ‘month_order’ column by ‘IF’ function:

month_order = 
IF(
    Sheet1[datediff] = 0,
    1,
    Sheet1[datediff] + 1
)

The result is as follows:

Best regards,

Lionel Chen

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

[danielbrownau]

Hi @v-lionel-msft 

Perfect! Thank you for the assistance, works like a charm, and even over several past years.

Thank you again.

DB

[v-lionel-msft]

Hi @danielbrownau ,

I think it's better to use RANKX() function:

Column = 
VAR now_date = MONTH(NOW())
RETURN
RANKX(
   ALL(Sheet1),
   DATEDIFF(now_date, Sheet1[date], MONTH),
    ,
    1,
   Dense
)

DATEDIFF(): https://docs.microsoft.com/en-us/dax/datediff-function-dax

RANKX(): https://docs.microsoft.com/en-us/dax/rankx-function-dax

Best regards
Lionel Chen