cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tykurzy
Frequent Visitor

Lose Custom Date/Time Excel Format (dd hh:mm) When Imported to Power BI

Hi there,

 

Working on calculating a time to resolution metric for our help desk by subtracting the "Completed By" system date and time from the "Created On" system date and time. 

 

Time to Resolution = Completed By - Created On

 

Time to Resolution is a custom format in Excel (dd hh:mm)

tykurzy_0-1636584374793.png

 

In Excel, I can sum, average, and count the Time to Resolution metric.

 

When imported into Power BI Desktop, it changes the format to:

tykurzy_1-1636584577190.png

 

I've tried different change types and transform options, but am having trouble. I'm fairly new to Power BI and am unsure if I can create a calculated column with a custom date format that can be used to sum and average, just like in Excel.

 

Any help would be very much appreciated.

3 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @tykurzy 

 

When converting to DateTime, you should use Change Type -> Using Locale

usinglocale.png

 

If your dates are in dd/mm/yyyy format then use a locale that matches like English (Australia) or English (United Kingdom)

usinglocale2.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

PhilipTreacy
Super User
Super User

Hi @tykurzy 

 

Download sample PBIX file

 

In this file I've imported your Excel file then converted the Created On and Completed By columns to DateTime Using Locale.

 

I've then subtracted one from the other in PQ and changed that resultant column, called Time to Resolution, to Duration type.

restime2.png

 

 

Once loaded into the data model, the Time to Resolution will be displayed as a decimal, because that's how it is stored.  DAX doesn't have ther Duration data type.

 

In order to display these durations in a human friendly format, I've used some code to do that formatting.  I've created a measure called Resolution Time

 

 

Resolution Time = 

VAR Elapsed_Time = SELECTEDVALUE('Transform'[Time To Resolution])
VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)

VAR seconds = INT((_mins - mins) * 60)

RETURN

//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "  & FORMAT(seconds,"00") & " s"  

 

 

 

With this added to a table visual, your resolution times are easier to understand

restime.png

 

You can change the final format as desired.  Some background on this measure and durations in DAX

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub

 

You can remove the Time to Resolution column from this table.  I left it in just to illustrate what it looks like before formatting with my measure.

 

When you open the PBIX, you'll of course need to change the Source step to point to your Excel file.

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Hi @tykurzy 

 

Download sample PBIX

 

Use this measure 

Avg Resolution Time = 

VAR Elapsed_Time = CALCULATE(AVERAGEX('DataTable','DataTable'[Time To Resolution]), FILTER(ALL('DataTable'), 'DataTable'[Owner First Name] = SELECTEDVALUE('DataTable'[Owner First Name])))

VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)

VAR seconds = INT((_mins - mins) * 60)

RETURN

//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "  & FORMAT(seconds,"00") & " s"  

 

avgsm.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

10 REPLIES 10
tykurzy
Frequent Visitor

Thanks @PhilipTreacy! Looks like I need to start learning DAX. 

 

@Daniel_Fdrvc, check out Phil's solution. Worked well for me.

tykurzy
Frequent Visitor

Thanks for your help, @PhilipTreacy

 

I'm almost there. 

  1. Successfully implemented @PhilipTreacy  solution by formatting dates using English (UK) under "Using Locale"
  2. Successfully implemented @PhilipTreacy "human-friendly" format (d h m s)


My next challenge is in building my visualization...

tykurzy_0-1636746817057.png

As you can see above, I am averaging the Response Time by help desk employee. This is fantastic but 4.32 is not easily understood. How do I apply Phil's human-friendly formatting d / m / h / s to this simple table visualization?

 

Appreciate the help!

Tyler

Hi @tykurzy 

 

Download sample PBIX

 

Use this measure 

Avg Resolution Time = 

VAR Elapsed_Time = CALCULATE(AVERAGEX('DataTable','DataTable'[Time To Resolution]), FILTER(ALL('DataTable'), 'DataTable'[Owner First Name] = SELECTEDVALUE('DataTable'[Owner First Name])))

VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)

VAR seconds = INT((_mins - mins) * 60)

RETURN

//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "  & FORMAT(seconds,"00") & " s"  

 

avgsm.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @tykurzy 

 

Download sample PBIX file

 

In this file I've imported your Excel file then converted the Created On and Completed By columns to DateTime Using Locale.

 

I've then subtracted one from the other in PQ and changed that resultant column, called Time to Resolution, to Duration type.

restime2.png

 

 

Once loaded into the data model, the Time to Resolution will be displayed as a decimal, because that's how it is stored.  DAX doesn't have ther Duration data type.

 

In order to display these durations in a human friendly format, I've used some code to do that formatting.  I've created a measure called Resolution Time

 

 

Resolution Time = 

VAR Elapsed_Time = SELECTEDVALUE('Transform'[Time To Resolution])
VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)

VAR seconds = INT((_mins - mins) * 60)

RETURN

//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "  & FORMAT(seconds,"00") & " s"  

 

 

 

With this added to a table visual, your resolution times are easier to understand

restime.png

 

You can change the final format as desired.  Some background on this measure and durations in DAX

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub

 

You can remove the Time to Resolution column from this table.  I left it in just to illustrate what it looks like before formatting with my measure.

 

When you open the PBIX, you'll of course need to change the Source step to point to your Excel file.

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @tykurzy 

 

When converting to DateTime, you should use Change Type -> Using Locale

usinglocale.png

 

If your dates are in dd/mm/yyyy format then use a locale that matches like English (Australia) or English (United Kingdom)

usinglocale2.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @tykurzy 

 

Download example PBIX file

 

If you subtract one DateTime from another you should end up with a duration in Power Query.  This is a decimal number of days that is displayed in the format 1.12:30:00 meaning 1 day, 12 hours, 30 minutes, zero seconds.

 

You should change your Created On and Completed By columns to the DateTime type before doing the subtraction.

datetime1.png

datetime2.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks, Phil. Trying to implement your solution but getting error messages when trying to change my date columns from text to Date/Time. 

 

I suspect Power Query thinks my date field is mm/dd/yyyy; whereas the system date format I'm pulling in is dd/mm/yyyy.

 

Anything with a value over 12 in the "dd" part of the system date receives an error message.

 

Here is the error message: 

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
14/11/2018 10:53:04

 

tykurzy_1-1636660380928.pngtykurzy_2-1636660440804.png

 

Thanks for your help,

Tyler

Can you upload here that excel file with some dates? Maybe this will make it easier to replicate your problem and find a solution faster.

Hi Daniel,

 

Link to file: IT Tickets Database.xlsx

 

Thanks,

Tyler

I'm new in PBI too, but founded some, maybe not user friendly solution to convert dates when they in format mm/dd/yyyy via new calculated column:

 

Converted date 1 = mid(Sheet1[Date 1];4;2) &"/"& left(Sheet1[Date 1];2) &"/"& mid(Sheet1[Date 1];7;4) &" "& mid(Sheet1[Date 1];12;2) &":"& mid(Sheet1[Date 1];15;2) &":"& mid(Sheet1[Date 1];18;2)
 
This slices and connect date from parts and then i can convert to date-time. From now i can calculate time to resolution. Maybe this will be helpfull for you.
 
converted dates.JPG
 
I have imported your file to and tried to convert Time to Resolution column to dd hh:mm. By the way you can do this here:
 
dd hh mm.JPG
 
But when this is aplied, everywhere where difference between two dates is 0 (resolved at same day) there it shows 31 days. Maybe some advanced user can explain why... It will be helpfull for me too for the future 🙂
 
31.JPG
 
Hope we will find a solution together.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!