Forum Discussion
DATE format / sort date issue
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.
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?
Basically - in my data table I have only 1 column refere to date - formated as date:
I have separate DIM_Calendar with a lots of columns. Index Colum (id_Date) it's excel date formated as integer. So in excel columns ix_Date and Date have same values just different format.
In same table I have 2 columns - RANK and DAYNO. Rank is number, DayNo is text.
When I try sort DayNo by Rank I got obvious message:
So I created sub-Dim table called Days and there I set sortation I expect.
But this works only if I am inside ONE month. When I use weeks and I have split months in week it not works as I need.
the message is not related to same day numbers across different months, it's pointing that for some DayNo there are multiple different Ranks, e.g. DAyNo 09 has Rank 9 and null, etc.
you mention that you have weeks as well - if what you try to sort is day + week the sorting column will need to be in exactly same granularity
EDIT - I see it in your 3rd screenshot - you sort day ranging 01-31 by Rank based on Date, which has values like 457-459, which is wrong. You need a separate rank field ranging 1-31Hi AdamBoltryk
As tested, i can sort the table by "date" column, This can be achieved in query editor. then you can create in index column.
For each visual which adds "date" column in, you can sort by "date".
However,when you only add "dayno" and "week" in a visual, it can't be sort correctly, Because there are more than one value in "date" for the same value in "dayno".
So your method is a workaround someway.
Best Regards
Maggie
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.
