Sort Day-Month Error

upwardD · December 22, 2021, 2:12pm

Hi,
Trying to sort Day-Month column and it is strange that it is not working out to be quite easy like Month-Year.

I have tried googling for this solution and none is working for me. I do need your help.
PBI file attached.
Day_Month_Sort.pbix (319.2 KB)

Thank you

Melissa · December 22, 2021, 2:38pm

Hi @upwardD,

The granularity doesn’t match between the Date[Day & Month] and Date[DayMonthSort] columns.
A Sort By column needs to be an equivalent of that what needs to be sorted so Date[Day & Month] would translate to:

FORMAT( CONVERT( 'Date'[WeeK Commencing], DATETIME ), "ddmm" )

Here’s your sample file.
Day_Month_Sort.pbix (320.2 KB)

I hope this is helpful

upwardD · December 22, 2021, 2:43pm

Thank you @Melissa for the reply.
I got an error while using the code above:

Thank you

Melissa · December 22, 2021, 2:47pm

Attached the sample in my previous response.
I hope that helps

upwardD · December 22, 2021, 2:54pm

I am very grateful!
Thank you @Melissa

Melissa · December 22, 2021, 3:08pm

Glad I could help

upwardD · December 23, 2021, 2:37am

Hi @Melissa, I am sorry I’m coming back to this again. I noticed some discrepancies in the sort order. Please see the image attached and the highlighted bars.

I look forward to your reply
Grateful!

Melissa · December 23, 2021, 7:24am

Right, give this a go…

DayMonthSort = 
VAR d =  SUBSTITUTE( 'Date'[Week Commencing], "/", "|")
VAR n = PATHITEM( d, 2, INTEGER )*100 + PATHITEM( d, 1, INTEGER )
RETURN  n

Set column type to: Whole number

I hope this is helpful

upwardD · December 23, 2021, 8:15am

Thank you @Melissa, it works perfectly!!
Grateful