Forum Discussion
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
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)
5 Replies
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.
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 )Note: this is a TEXT format.
Best Regards!
Dale
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 ?
I have found my mistake, I chosse the wrong visual :smileyembarrassed:
