Forum Discussion
Data view - date display formats
I seem to be running into a problem I was previously having here:
https://community.powerbi.com/t5/Desktop/Default-sort-column-of-table-in-report/td-p/63901
I thought I had it solved there but I just noticed my dates are not sorting correctly again so perhaps I'll start a new thread as my structure has changed a little from when I started that thread.
What I have:
Working in the DATA view:
I have a column (generated from the query) with Date/Time:
and formatted as Date/Time
What I want in my table - in order to display in a report is a date pre-appended with the three letter day abbriviation. So, I created a new column called DayDateTime: DayDateTime = FORMAT([DateTime], "ddd DD/MM/YYYY hh:mm") but this seems to conflict with the display format - I can display it as Text but then it does not sort correctly.
I cannot convert it to date format because I don't seem to have the option to specify my custom format "DDD DD/MM/YYYY hh:mm" nor is that format supplied as a selection in the list. This is what I get:
Am I stymied? Can I not have the day preappended to the date? I thought I had acheived that as per my thread referenced at the top of this post but it seems I had not after all.
Thanks in advance for help/advice.
Karl
16 Replies
Try the following :
1. select the newly created colum DayDateTime
2. Go to the sort by column
3. Choose the original column DateTime
This should work.
If it works please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Hi Anonymous thanks for your response. I wish it were that easy. This is what I get when I do as you suggest:
This issue is obiously that I have a number of rows in DayDateTime with the same day date time but I need those to remain as they are, otherwise I could just append a random number to the end of them.
I thought you had a date table.
Try this.
1. Create a calendar(date) table from the transactiondate in fact table.
2. Join the Calendar Table and the Fact Table on the datekeys.
3. Create the new column in the fact table
4. Set the sort by column to TransactionDate in Fact table
this should work
Cheers
CheenuSing
OK so I have taken one step forwards and have two steps to go...
It turns out that the reason I was getting the "We cannot sort DayDateTime by DateTime..." error is because in my source column I have dd/MM/YYYY hh:mm:ss but in my target I had not declared the :ss (seconds). DayDateTime = FORMAT([DateTime], "ddd dd/MM/YYYY hh:mm tt") results in the "We cannot sort..." error but DayDateTime = FORMAT([DateTime], "ddd dd/MM/YYYY hh:mm:ss tt") allows me to sort by the other column. It appears that you have to have exactly the same number of elements in source and target. That makes sense I guess but it then works against what I want to acheive.
I can now sort my target column, which includes my day abbreviation. However I now have two issues:
- I want to disply the DayDateTime without seconds however when I remove seconds from my FORMAT statement I can not sort DayDateTime by DateTime. My source data has seconds included and so I need to include it in the targe column but I don't want to display seconds - how can I remove them from the display?
- According to here: http://social.technet.microsoft.com/wiki/contents/articles/685.power-bi-dax-text-functions.aspx#predefined_datetime "tt" in the statement "displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M". I am in a Locale which uses AM/PM format and I have checked the file options to make sure the Locale is set correctly (Australia) so why does it display as this?:
for this statement:
