Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi,
I have two columns:
1. Start time
2. End time.
I want to know the duration of time between the two columns in hour and minute format (HH:MM)
Then I want to display their summary on the "CARD" in the same format.
Attached is a sample example.
Thank you so much for helping.
BR,
Alon
Solved! Go to Solution.
Duration HH:MM:SS = // We start with a duration in number of seconds VAR Duration = DATEDIFF( 'Table'[StartDate], 'Table'[EndDate], SECOND ) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) )
This is code from:
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Best
Darek
This was a great solution. Worked like a charm!
Hi,
Thank you for your answer,
The result is good, but I have to summarize the hours and minutes.
I don't seem to be able to do that because this field is text.
BR,
Alon
Hi @Anonymous,
Do you have an example of such a calculation ..?
Hi,
I created a calculated column that sums up the seconds between the two columns
Which part of your code should I replace not so clear ..?
"So, you should create a measure like [Total Seconds] = SUM ( T[Seconds] ) and then use its value in my measure replacing the part which is responsible for calculating the seconds..."
Use Its value in my measure replacing the part which calculates the seconds...
I think this is as clear as the sun. Your "effort" is to find the part that calculates the seconds and substitute the value of the measure for the number of seconds. Can't be any simpler than that.
Best
Darek
Hi @elads
The below Measure does the sum ( SUMX part ), the rest is just formatting it to DD - HH:MM:SS, technically you can use SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1 part on its own, however this would give you only decimal value.
Measure = VAR _all = SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1 VAR _days = INT( _all ) VAR _time = _all - _days RETURN FORMAT( _days, "#0D-" ) & FORMAT( _time, "hh:mm:ss")
Hi,
Sorry, I answered you..
Hi @elads
You can do something like below, you can adjust the code to show only hh:mm if you wish, but the below will address the issue of days when the sum goes over 24.
Measure = VAR _all = SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1 VAR _days = INT( _all ) VAR _time = _all - _days RETURN FORMAT( _days, "#0D-" ) & FORMAT( _time, "hh:mm:ss")
Thnaks for information
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.