Forum Discussion
Custom Sort Order for a Measure
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.
I meant you can FORMAT the date in your measure.
FORMAT(SELECTEDVALUE(...), "yyyy/MM/dd")
Pat
7 Replies
Anonymous , Mark the datetime column (or take out from this column) and mark that sort column for Status Not Date
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.
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?
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
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.
I meant you can FORMAT the date in your measure.
FORMAT(SELECTEDVALUE(...), "yyyy/MM/dd")
Pat
