Custom Sort Order for a Measure

[eloomis]

I have the following measure which returns the screenshot of data below when applied to a matrix - essentially the date with a little calculation of how many days old the note is. 

StatusNoteDateFormatted = 
VAR DaysOld = 
    CALCULATE(
        DATEDIFF(
            SELECTEDVALUE('#Success - Project List'[ProjectStatusNoteDate]), 
            TODAY(),
            DAY
        )
    )
RETURN
        IF(
            DaysOld = BLANK(), 
            BLANK(), 
            SELECTEDVALUE('#Success - Project List'[ProjectStatusNoteDate]) & " (" & DaysOld & " Days Old)"
        )

When you click the matrix header to sort chronologically, it sorts numerically rather than by the date.

i.e. 
5/9

5/8

5/5

5/3

5/12

5/11

I would like it to sort chronologically, oldest to newest or vice versa. 

I found this article which shows how to introduce a custom sort order to a measure but they only have 3 specific categories wheras my measure is dynamic based on the value of Status Note Date. Does anyone have any other methods similar where I could make this measure sort by date (or even by the count of days old) rather than numeric order when the column header is clicked?

Thank you in advance.

[ppm1]

It is sorting alphabetically (descending). You can FORMAT your date as "MM/dd/yyyy" so that the zeros show up and you get the needed order.

Pat

[eloomis]

Hi @ppm1 thanks for your response. So the sort by column (StatusNoteDate) I changed the date format to "MM/dd/yyyy". I can't format the calculated column (StatusNoteDate&DaysOld) that way since I am adding a string, it can't be both date and string. But if I sort the Calculated column by StatusNoteDate, it sorts in the correct order in data view but is still going alphabetical in the visual.

[ppm1 · Accepted solution]

I meant you can FORMAT the date in your measure.

FORMAT(SELECTEDVALUE(...), "yyyy/MM/dd")

Pat

[eloomis]

Oh I understand now, thank you. This works!!

[eloomis]

@amitchandak I converted my measure to a calculated column so that I could use the method you described. I'm still having a problem though. In data view, you can see that the calculated column is being sorted by ProjectStatusNoteDate (which is marked as a date field) and the sort is in the correct order.

However, when I go to my visual it is still out of order. In descending order, we see 5/5, 5/3, 5/2, then 5/11, 5/10, 4/29 and so on.

I've tried resorting the visual many times and continue to get the same result. Is there anything else I can try?

[amitchandak]

@eloomis , Mark the datetime column (or take out from this column) and mark that sort column for Status Not Date

[eloomis]

Hey @amitchandak, thanks for your reply.

Unfortunately Status Note Date Formatted is a measure which is the column you see in the visual (Status Note Date). I was under the impression you can't define a column to sort a measure by.