Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
While taking the first baby steps into Power BI, I was trying to get grips with how to sum time spent on a particular project and got stuck.
Say that I have a table where each row represents different time entries, each being assigned to different projects, as below.
Creating a measure to sum the duration would be expected to return a sum of 36h, which is not.
It is showing 12h instead. I understand that it may be deducting the first 24h from the lot. Is there a way that I can format the SumOfDuration column to keep adding up the hours and report values such as [Days][Hours][Minutes]?
Thank you for your help.
Solved! Go to Solution.
I was able to get it to work:
I did two things. I created a measure SumOfTime defined as above. And I set the format of the Time field and of this measure to "(H:mm)".
To get the expected output, you can follow below steps.
newDuration = VAR TotalSeconds=SUMX('Sheet2 (2)',HOUR('Sheet2 (2)'[CopyDuration])*3600+MINUTE('Sheet2 (2)'[CopyDuration])*60+SECOND('Sheet2 (2)'[CopyDuration])) VAR Days =TRUNC(TotalSeconds/3600/24) VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600) VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60) VAR Secs = MOD(TotalSeconds,60) return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)
Hi again. just noticed that once i started to filter, it actually changed.
sorry for that.
But now i'm having issues to get it to show average (can't understand what you explain above)
and also i want to add this to a column chart. and right now it only counts the measure and column as it is in text type.
but i can't change it to anything else as it will only result in error.
what am i missing?
thank you in advance.
Hi,
I'm working with time intervals in PowerBi. This topic was very useful for me but I need a little more help from you.
I have a table in the model (Orders) with #Order, StartTime, EndTime and ElapsedTime in seconds and I need to visualize the total elapsed time in format hhh:mm:ss (Elapsed Time is usually greather than 24 hours)
I did two measures to see the problem:
The first (Without Text) realize the calculations to express elapsed time in the new format, but I've trimmed the text formatting for testing purposes.
Without Text = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)]) VAR Hours = INT(Duration/3600) VAR Minutes = INT(MOD(Duration;3600)/60) VAR Seconds = MOD(Duration;60) RETURN CONCATENATE (Hours; CONCATENATE (Minutes;Seconds ))
The Second (Real Time), do the same calculations, but I've added format to display elapsed time in hhh:mm:ss
Tiempo Real = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)]) VAR Hours = INT(Duration/3600) VAR Minutes = INT(MOD(Duration;3600)/60) VAR Seconds = MOD(Duration;60) 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 ) ) VAR tH = IF(Hours=0;"";IF(Hours>1;Hours&"h: ";Hours&"h: ")) VAR tM = IF(Minutes=0;"";IF(Minutes>1;Minutes&"m: ";Minutes&"m: ")) VAR tS = IF(Seconds=0;"";IF(Seconds>1;Seconds&"s";Seconds&"s")) RETURN CONCATENATE (th; CONCATENATE ("";CONCATENATE (tm;CONCATENATE (""; CONCATENATE (ts;"" )) )))
Now..
In Table 1 you see that "Without text" measure works perectly but show unformatted data (obviously).
In Table 2 you see that "Tiempo Real" measure shows formatted data but.... I've lost de filter context.
How do I to fix this? My brain burns...
Hi mate
Is this still a problem for you?
Hello Acyrus1992,
Thank you very much again!!! for the valuable information.
Yes, I'm using it for ITSM database and incident table, however i'm using your code to utilizing for calculating MTTR in hours and but actually not for technician logged hours.
I'm trying calculate the incident duration i.e MTTR-W/E - (Mean Time To Resolve With Excludes) ticket duration which excludes time period awaiting for 3rd party and awaiting for user for particular ticket.
When I download the incident data from ITSM DB into excel in seconds format and I have calculated MTTR very easily in excel as it directly gives in seconds format(Screenshot below).
However, when I'm using ITSM DB - import method via Azure SQL DB to build PowerBI dashboard to reduce manual efforts, it has given in months, days, hours, & mins format which is impossible to calculate MTTR of tickets.
Please find screenshot of excel sheet calculated column and pivot table.
Pivot Table
Cheer's,
Nawal
+91-9866755720
Hi @Anonymous
OK - lets start from the top.
In PowerBI Desktop - do you have a column with jsut the seconds in it? (imported from DB?)
Or - Do you have the "Start Date" and "End Date" from the DB? (From which you can get seconds (End Date - Start Date)?
If you do - we can get the total seconds figure by basically doing a simple calculation like this: [CONVERT TO SECONDS(End Date - Start Date)] - [(3rd party time/unSupported Time in seconds)]
What data do you have available to use in the Data View?
Hello Acyrus1992,
In PowerBI Desktop - do you have a column with just the seconds in it? (imported from DB?)
> Nawal - Nope, the data isn't in seconds format when I imported from DB. Its in Days hours minutes format
Or - Do you have the "Start Date" and "End Date" from the DB? (From which you can get seconds (End Date - Start Date)?
> Nawal - It does Not have start date and end date. Its just duration of ticket.
What data do you have available to use in the Data View?
> Nawal - data is in below format
Cheers
Nawal
Hi @Anonymous
Where are you getting the Total Elapsed Time from?
Is the only (date/time/duration) related data you have available in PowerBI Desktop - Data View - this :
> Nawal - data is in below format
Hello Acyrus1992
Where are you getting the Total Elapsed Time from?
> Nawal - When I download it from reporting to excel, its automatically converting in to seconds format and I use formula to convert from seconds to hours by (column name / 3600).
I'm really not sure, how its converting.
Is the only (date/time/duration) related data you have available in PowerBI Desktop - Data View - this :
> Nawal - yes its only 1 column with duration, however its in text format.
If you are OK, I can share you my screen.
My skype ID: snawalkishore@hotmail.com.
Cheers,
Nawal
Hi @Anonymous
I have build a solution for you (I think)
You will need to create 4 new Columns in your Dataview.
Do this:
Column1:
TIME_Days = (IF(FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number")="","0",FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number"))) * 1440
Column2:
TIME_Hours = SUBSTITUTE(IF(SEARCH("Hours",TestData[Actual_Elp_Duration],1,"0")=0,"0",IF(SEARCH("Days",TestData[Actual_Elp_Duration],1,0)<>0,RIGHT(LEFT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Days",""),5),3),LEFT(TestData[Actual_Elp_Duration],2)))," ","")*60
Column3:
TIME_Minutes = (IF(SEARCH("Minutes",TestData[Actual_Elp_Duration],1,0)=0,"0",RIGHT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Minutes",""),2))*1)
Column4:
TIME_Total_Minutes = TestData[TIME_Days]+TestData[TIME_Hours]+TestData[TIME_Minutes]
This should give you you total times in a int format of minutes 🙂
Hello Acyrus1992,
I do not have words...
I'm really thankful to you. Its working.
I will be in touch with you. I'm Beginner of PowerBI. I'm sure I would definitely request your expertness when I run into Logical or arithmetical issues.
Thank you,
Nawal
+91-9866755720
This seems to work. Thank you very much for your time.
So, in order for me to learn "how to fish" rather than just using the fish given (apologies for some misuse of idioms)...
What you did was creating a new measure "newDuration".
You then set out to create different variables VAR to make it easier to use on the IF THEN expression below.
First VAR converts the Date/Time value into seconds only, which are then used to convert the other VARs (days, hours, mins, secs)
You truncated (TRUNC) each VAR in order to display integer values for each of the time units.
Is this it? And because the newDuration is a Date/Time field I can used it to drill down data and use it for any other date/time analysis for my data?
Is this it? And because the newDuration is a Date/Time field I can used it to drill down data and use it for any other date/time analysis for my data?
Yes. And newDuration is not a Date/Time, it is a text. Because"x days 00:00:00" is neither a valid date nor a time format, so I think you can't use it as Date/Time and drill down.
So, I may need to create different calculates columns, then, as I may need to report things differently. Or I may still misintepreting what are my options here. I explain.
One with values formated as Date/Time, or just Time, where I get a whole number for the time unit I care (minutes or hours, say). For this column I would need to first convert all Date/Time data into that time unit first. But after that I can create different measures such as Sums, Averages, whatever. And these can in turn be used on the production of visuals.
Another column with values formated as Text, where I use the "newDuration" to display the data broken down into days, hours, mins. This column would be used whenever tables and matrixes are involved.
All this because it is not currently possible to get a Date/Time field where one can extract duration values directly.
Does this make sense?
Joaquim
I was able to get it to work:
I did two things. I created a measure SumOfTime defined as above. And I set the format of the Time field and of this measure to "(H:mm)".
Thank you for the feedback.
That one works because the sum is less than 24h.
Try adding more rows so that the SumOfTime is larger than 24h. Does it report that figure?
Sorry, I misinterpreted your statement. What happens is that the Time data type is for representing the hours in a day, so it will not "accumulate" as you wish it would. It is not meant for representing an arbitrary number of hours. I think you will have to convert it to a decimal number, as in 8:30 being 8.5. Or include dates, if it's possible.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |