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
danielvt
Advocate I
Advocate I

Problem with the measure to summarize time data

Hi there,

I have already found a lot of entries regarding the measures and time data but as far as I can see not the right one.

I tried to summarize time data with a measure. The pictures shows the source data and the results.

I have already created a new field "time2" with the right time format so that only the time is shown without the date.

The date seems to be added automatically by Power BI because the original source data(excel file) only includes the time without a date.

So I have added the additonal column time2, so that only the time is used.

But as you can see at the first pitcure if I try to summarize the time2 data with a measure Power BI added a date. Is there a possibility that the date is not shown?

 

Best regards,

Daniel

 

Picture_FrontEnd.pngPicture_Data.png

1 ACCEPTED SOLUTION

Hi there,

after looking at some other entries, I have found a solution which I could use after some adjustments:

https://community.powerbi.com/t5/Desktop/Sum-of-H-MM-SS/m-p/125204#M53009

 

Time =
VAR TotalSeconds=SUMX('Sheet3 (2)';HOUR('Sheet3 (2)'[totalTimes])*3600+MINUTE('Sheet3 (2)'[totalTimes])*60+SECOND('Sheet3 (2)'[totalTimes]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hours = HOUR('Sheet3 (2)'[SumTime])
VAR Mins = MINUTE('Sheet3 (2)'[SumTime])
VAR Secs = SECOND('Sheet3 (2)'[SumTime])
return IF(DAYS=0;"";IF(DAYS>1;DAYS&" days ";Days&" day "))&IF(Hours<10;"0"&Hours;Hours)&":"&IF(Mins<10;"0"&Mins;Mins)&":"&IF(Secs<10;"0"&Secs;Secs)

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

@danielvt,

 

Hi Daniel,

 

That's because the total of the times is bigger than 24 hours. For example, 16: 00: 00 + 16: 00: 00 = 32: 00: 00? It's obvious not a time in time format. The right answer is 1 day 8 hours. It shows up "12/31/1899 08:00:00" in Power BI.Problem with the measure to summarize time data.JPG

 

 

 

 

 

 

 

 

 

 

 

 

The workaround could be: (Replace totalTimes with yours.)

 

Measure 25 =
VAR totalTimes =
    TIME ( 22, 59, 45 ) + TIME ( 23, 12, 0 )
VAR seconds =
    DATEDIFF ( DATE ( 1899, 12, 30 ), totalTimes, SECOND )
VAR days =
    INT ( seconds / 86400 )
VAR timepart =
    FORMAT ( TIMEVALUE ( totalTimes ), "hh:mm:ss" )
RETURN
    CONCATENATE ( CONCATENATE ( days, "day: " ), timepart )

Problem with the measure to summarize time data2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note: this is a TEXT format.

 

Best Regards!

Dale

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

Hi there,

thanks for your reply. I have tried to rebuild you script(See attached picture) but it doesn't work. I get no result.

Even if I try to rebuild your first example.

I had to replace the commas with a semicolon that the scripts are valid.

How does you source data look like or did you already see my mistake ?Picture1.pngPicture2.png

I have found my mistake, I chosse the wrong visual Smiley Embarassed

The Problem is now that the calculation just display the total.
If I add a additional field the measure only display the same total value for all fields

 

Picture1.png

Hi there,

after looking at some other entries, I have found a solution which I could use after some adjustments:

https://community.powerbi.com/t5/Desktop/Sum-of-H-MM-SS/m-p/125204#M53009

 

Time =
VAR TotalSeconds=SUMX('Sheet3 (2)';HOUR('Sheet3 (2)'[totalTimes])*3600+MINUTE('Sheet3 (2)'[totalTimes])*60+SECOND('Sheet3 (2)'[totalTimes]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hours = HOUR('Sheet3 (2)'[SumTime])
VAR Mins = MINUTE('Sheet3 (2)'[SumTime])
VAR Secs = SECOND('Sheet3 (2)'[SumTime])
return IF(DAYS=0;"";IF(DAYS>1;DAYS&" days ";Days&" day "))&IF(Hours<10;"0"&Hours;Hours)&":"&IF(Mins<10;"0"&Mins;Mins)&":"&IF(Secs<10;"0"&Secs;Secs)

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.