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.
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-31