Automatically convert column data

[mwen90]

Hi, 

Thanks for the help! I feel like this should be easy but I can't find the DAX. 

I have two columns (text) which display time like 12.25 and 13.50. I want to convert these automatically (as data comes in) so it display's in quarters so 12.15 and 13.30 so I can do time lost calculations. Ie. 13.30-12.15 = 1.15, so it's one hour and 15 lost. 

How do I convert these numbers?

Thanks for the help!

[Anonymous]

Hi @mwen90 

First, you need to change the two columns from integer format to time format .

Original data :

Create calculated column :

time1 = FORMAT(TIME(TRUNC(Sheet1[Column1],0),(Sheet1[Column1]-TRUNC(Sheet1[Column1],0))*60,0),"")

time2 = FORMAT(TIME(TRUNC(Sheet1[Column2],0),(Sheet1[Column2]-TRUNC(Sheet1[Column2],0))*60,0),"")

The effect is as shown:

Then calculated the diff between two time , and change the format .

diff time = FORMAT(Sheet1[time2]-Sheet1[time1],"hh:mm:ss")

The final result is as shown:

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd · Accepted solution]

Hey @mwen90 ,

as you said the time is text. So you can split it up and compare.

Try the following calculated column:

Time Bucket =
VAR vHours = LEFT( myTable[TimeColumn], 2 )
VAR vMinutes = RIGHT( myTable[TimeColumn], 2 )
RETURN
    SWITCH(
        TRUE(),
        vMinutes > 0 && vMinutes <= 15,
            vMinutes & ":" & 15,
        vMinutes > 15 && vMinutes <= 30,
            vMinutes & ":" & 30,
        vMinutes > 30 && vMinutes <= 45,
            vMinutes & ":" & 45,
            vHours + 1 & ":" & 00
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic