Help with Date/Time + Text Concatenate Sort in Chart Visualization - out of order due to 1 in month

[apmulhearn]

Hi,

Below is a chart visualization and the issue I'm trying to fix.
"New Subject" is a combination of Year/Month and a subject line.

I want the ones highlighted in yellow to be at the top when sorted in descending order, as 2022-11 is more recent than 2022-3. But the 1s are distorting their position. 


The syntax for creating the column is: 

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject]

[rsbin]

@apmulhearn ,

What you need to do when creating Year-Month is to ensure they are the same number of characters.

If the Month is less than 10, add a "0" into your String.  This will then enable you to sort Year-Month properly.

Year-Month = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone))< 10, 
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],

If the Month is less than 10, add a "0" into your String.  This will then enable you to sort Year-Month properly.

Regards,

[apmulhearn]

Hi! This makes sense conceptually for sure but I'm missing something in the syntax. Can you help me?

This is what is in my original NewSubject column:

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject]

And this is what I put in the new column, which is resulting in an error.

Year-Month = SWITCH(

                TRUE(),

                month('Top 300 and Virt Stats'[Sent At (Your time zone)])< 10,

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject])

[rsbin · Accepted solution]

@apmulhearn ,

The SWITCH function is structured like this:

SWITCH(
         TRUE(),
          Condition1, Result1,

          Condition2, Result2,

          Else all other conditions.

https://learn.microsoft.com/en-us/dax/switch-function-dax

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

New Subject = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone)])< 10, 
// This is first condition - if Month is less than 10, then you note I add the "-0" in your result

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
// This is the result if condition is true.

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject])
// This is the result if no conditions are met.  That is if Month >= 10, then just use "-"

The "//" indicates a comment, just for explanation purposes.