Forum Discussion
DATE format / sort date issue
Hi All,
Maybe this Community can help me with my problem :)
As you can see below, PBI sort days wrongly. From other hands it's sorted correctly as sortation order is 1..31 At this moments 01,02,03 etc are labels/text (as PBI do not allow me to set date format i wish DD), moved into 2nd DIM table and sorted 1...31 what is OK from arthemtic but wrong from calendar point of view.
How I can solve this problem? Any idea?
Adam
I just want share with all how I solve this "problem". I've been created new column with veeeeeeeeeeeeeery long input:
28_______________________________________________________28/01/2018
29_______________________________________________________29/01/2018
30_______________________________________________________30/01/2018
31_______________________________________________________31/01/2018
01_______________________________________________________01/02/2018
02_______________________________________________________02/02/2018
03_______________________________________________________03/02/2018
04_______________________________________________________04/02/2018
etc...
and now PBI automatically cut this fields showing only needed value:
It's not best/nice solution but it works :)
I am waiting for day I will be able to define my own date format, like "dd" :)
Regards.
7 Replies
you need to add numeric column with the sort order you require and use that for custom sort order in your text column
Maybe I did something wrong but I tried similar solution to this already. I set Rank column in my Calendar DIM and try sort my 01,02,03 texts byt this column. But PBI didn't allow me to do this as 'values in both columns have to be unique'... Obviously we have 01 for Jan, Mar, Apr etc. So I have extracted DIM table (called days) from calendar and I set sortation there 01..31. This works fine. Problem is when in same week I have 2 different months - ending one and starting next.
Any other ideas?
the granularity of the sorted column has to be the same - can you post your full calendar table and specify which column you've tried to sort by what?
