Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KarlinOz
Advocate III
Advocate III

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:
2016-09-16 10_06_57-Experiment5a - Power BI Desktop.png  and formatted as Date/Time 2016-09-16 10_08_21-Experiment5a - Power BI Desktop.png

 

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:

2016-09-16 10_15_50-Experiment5a - Power BI Desktop.png

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 16
KarlinOz
Advocate III
Advocate III

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:

  1. 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?
  2. According to here: http://social.technet.microsoft.com/wiki/contents/articles/685.power-bi-dax-text-functions.aspx#pred... "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?:

    2016-09-19 21_36_30-Experiment5a - Power BI Desktop.png   for this statement:
     2016-09-19 21_37_02-Experiment5a - Power BI Desktop.png

 

CheenuSing
Community Champion
Community Champion

@KarlinOz

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.

2016-09-16 16_02_55-Task Manager.png

@KarlinOz



What I want in my table - in order to display in a report is a date pre-appended with the three letter day abbriviation.

 

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.


In this scenario, I would suggest you to create a Day column to show the three letter day abbreviation only, then show Day column and DateTime column together in the report. See my sample below.

 

MyTestTable

t2.PNG

The data is sorted correctly in the report.

r3.PNG

Regards

Thanks @v-ljerr-msft yes that works and I had thought of it but I am still hoping there may be a more integrated way of acheiving this rather than having an entirely separate field, after all the day is part of the date and it would be disappointing if MS have not provided a means to configure the display of it outside of their supplied formats. 

@KarlinOz

 

Sorry for the late reply. 

 

I just added one more column called DateKey in the FactTable which is defined as

DateKey = (Year('FactTable'[DateTime])*10000+Month('FactTable'[DateTime])*100+day('FactTable'[DateTime]) )

 

Then set the DayDateTime to be sorted by this and I had no issues.

 

 

Check it out it should work.

 

CheenuSing 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@KarlinOz

 

You can submit your requirement on Power BI Ideas and vote it up. Smiley Happy

 

Regards

@KarlinOz

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing are you sayng to create a new table from the column DateTime in my 'Correspondance Register' table? I have not worked out how to do that yet. I select the DateTime column then hit New Table on the icon bar but then I am at a loss as to where to go from there. I guess I will try Googling that. I had tried Google for it in the past but didn't find a good instruction.

@KarlinOz

 

Creating a Calendar / Date table is as simple as writing

 

Calendar = CALENDAR (MINX('YourFactTable', [YourFactTableDate]),MAXX('YourFactTable', [YourFactTableDate]))

 

This will create the table Calendar in your data model with Date as a column.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thank you @CheenuSing and is 'YourFactTable' literally 'YourFactTable' ? or should that be the name of the table I am wanting to join to?

I feel like a baby, having to ask for each step, sorry.

 

I created the Calendar table, created a relationship between the 'Fact Table' and Calendar on the two date fields. The Calendar is the One side and the 'Fact Table' the Many. In my 'Fact Table' I created a new column: DateIndex = RELATED('Calendar'[Date]) which created ok but it is empty, no row has any data in it. What am I likely to have done wrong?

@KarlinOz

 

No worries. It is part of the learning process.

 

Folow the steps as follows

 

1.  Create a Calendar / Date table is as 

 

      Calendar = CALENDAR (MINX('YourFactTable', [DateTime]),MAXX('YourFactTable', [DateTime]))

 

              This will create the table Calendar in your data model with Date as a column.

 

2. Go the Modelling Tab in the power bi.

3. Go to Manage Relationship

4. Create relationship between the Date of Calendar Table and date column [DateTime] of YourFactTable

5. In YourFactTable create the column (which you might have already done)

     DayDateTime = FORMAT([DateTime], "ddd DD/MM/YYYY hh:mm")

 

6. Now set the Sort order for DayDateTime as [DateTIme] column of yourfactable.

7. Now plot the DayDateTime it should be in the proper order.

 

If it works please accept it as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thank you for your step by step instructions @CheenuSing, I have followed them however still at step 6 I ge the error:

2016-09-16 17_49_36-Experiment5a - Power BI Desktop.png

I can see what we are attempting to do - create a one to many relationship between the calendar table and the date field in the data table. So rows in the data table DayDateTime field will be sorted by just date in the Calendar table. Therefore Many rows in the data table will point to one row in the Calendar table.

 

2016-09-16 19_53_43-.png

 

2016-09-16 19_54_25-Experiment5a - Power BI Desktop.png

Hi @CheenuSing thanks for your response. I wish it were that easy. This is what I get when I do as you suggest:

2016-09-16 15_43_03-Experiment5a - Power BI Desktop.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.