Dynamic Format not working with Durations
In my reports, I use durations in seconds for various metrics. It is handy to create visuals and make calculations, but when displaying the values, users prefer to see "hh:mm:ss" instead of the number of seconds. I convert the durations to part-of-day decimal and use FORMAT() to display it as "hh:mm:ss" or "nn:ss" if less than an hour. But this does not work with dynamic formating: although i return a text string containing the appropriate format, it is handled as text instead of being applied to the decimal value as format stirng. So, how can i use dynamic formatting to apply the appropriate format string to a duration? The .pbix demonstrates the issue with a simple example (it's in weTransfer, can't upload here yet): https://we.tl/t-gdqaD1nf3d Thank you, NikosTrying to work out duration with date time columns and another with minutes
Hi there all, I'm trying to work out a total hours and minutes duration between a start time, end time and then add a to the duration. How can I do this in power bi desktop? (I only need the end calculation of the total time in hours and minutes). Thanks
Find Order Duration based on Start Stop time
Hello, please can you help to find order duration based on starting and finishing time example like in column duration. Order id From To Duration 0 23:00 23:03 1306993 23:03 23:03 1306993 23:03 23:06 1306993 23:22 23:33 1306993 23:33 23:33 1306993 23:34 23:35 1306993 23:35 23:37 1306993 23:38 23:39 1306993 23:42 23:43 1306993 23:43 23:43 00:40 0 23:43 23:44 1305300 23:44 23:45 1305300 23:45 23:46 1305300 23:47 23:47 1305300 23:48 23:49 1305300 23:54 23:54 1305300 23:54 23:55 1305300 23:56 23:59 1305300 23:59 00:00 1305300 00:00 00:01 1305300 00:02 00:03 1305300 00:03 00:03 1305300 00:04 00:04 1305300 00:04 00:07 1305300 00:07 00:07 1305300 00:07 00:08 1305300 00:08 00:09 00:25 0 00:09 00:10 1307012 00:53 00:54 1307012 00:54 00:55 1307012 00:56 00:58 1307012 01:02 01:05 00:12 0 01:05 01:05 1307031 01:05 01:05 1307031 01:06 01:06 1307031 01:11 01:11 1307031 01:13 01:14 1307031 01:19 01:42 1307031 01:44 01:45 1307031 01:45 01:46 1307031 01:46 01:46 00:41
Power BI DAX: Time Duration calculations displayed as [h]:mm:ss over 24 hrs (145:33:24)
I need assistance with calculating the sum of all the time taken for tasks being done. None of the existing solutions I have tried work as I need them to. The RADACAD example works for the card visual. I have a two tables that have the same columns but are for two different departments, I could append the tables as a single table, but I don't think it makes a difference at this point. The source files are imported from xlsx and the column Duration is General and appears as 03:10:10 It is then imported with Power Query using the Folder option and making changes to the sample file. I need to be able to sum this duration column in Power BI, however I do need to calculate total per user, filtering different dates. I have used transorm to duration, then in DAX SUMX and SUM the values using the var method, splitting and concatinating these values to RETURN a value. This is all good and well if I need to display the total on a card but does not work when using it in a table layout for users not generating data. My Employee table has everyone in, I can filter by department, group, function, manager or any of the other filter values. My data: Employee Duration Emp1 00:00:00 Emp1 null Emp2 00:00:08 Emp2 null Emp2 00:00:00 Emp1 00:26:51 Emp3 00:00:17 Emp4 00:00:09 My requirement is as follows: I need to display the value in hours exceeding 24 hours. As an example 145:33:24 When using the table visual, I drop the group and employeename in the rows field. I also use a slicer on group. In the values field, I cannot use the DAX calculated duration as it then provides everyones time in the rows. I could filter, lock and hide the department, but the problem I have using this is the employees with no data/duration shows the time as :: which is not a usable value and just looks wrong. The values are not usable in a chart as they are in TEXT format. I have spent a lot of time looking for and trying various solutions and none work with the charts or tables thus far. Please help? (Are there future plans for PBI format to include [h]:nn:ss so that it works beyond 24 hrs or Duration to be included in the table Data Type after applying transformation? I don't know who to direct this to as it is probably the devs.)Service Duration in seconds Calculation in DAX
Hi, I have this code in excel that calculates the service duration (in seconds) of a Support ticket, excluding bank holidays, weekends and out of office hours. Excel Code - =((NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$11)-1)*("18:00"-"7:00")+IF(NETWORKDAYS.INTL(B2,B2,1,$H$2:$H$11),MEDIAN(MOD(B2,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,$H$2:$H$11)*MOD(A2,1),"7:00","18:00"))*86400 My Dax code so far - Measure.ServiceHours = VAR _StartDate = SELECTEDVALUE(TICKET_MASTER[TICKETSUBMITDATE]) VAR _EndDate = SELECTEDVALUE(TICKET_MASTER[CLOSEDTIME]) RETURN ((NETWORKDAYS(_StartDate, _EndDate,1,BankHolidayDates)-1)*("18:00"-"7:00")+IF(NETWORKDAYS(_EndDate,_EndDate,1,BankHolidayDates),MEDIAN(mod(_EndDate,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(_StartDate,_StartDate,1,BankHolidayDates)*MOD(_StartDate,1),"7:00","18:00"))*86400 Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX? Example of excel code working below
