Measure to calculate the duration in days and time

[Hichamas4]

HI all,

I am currently busy to calculate the duration between startdatetime and enddatetime but in different format as 00 Days 00:00:00.

I am using the following measure but something goes wrong:

VAR     _StartUTC       =   MIN('Order'[From_UTC])  

VAR     _EndUTC         =   MIN('Order'[To_UTC])

VAR     _EndUTCBlank =   NOW()

VAR     _Duration        =   MIN('Order'[DayTime])

VAR     _DateDiff   =   DATEDIFF(_StartUTC,  _EndUTCBlank, SECOND)

VAR     _Day        =   INT(_DateDiff/ 86400)

VAR     _Hour       =   FORMAT(INT(MOD(_DateDiff, 86400) / 3600), "#00")

VAR     _Minute     =   FORMAT(INT(MOD(MOD(_DateDiff, 86400), 3600) /60), "#00")

VAR     _Second     =   FORMAT(INT(MOD(MOD(MOD(_DateDiff, 86400), 3600), 60)), "#00")

VAR     _Result     =   _Day & " Day " & _Hour & ":" & _Minute & ":" & _Second

RETURN

IF(ISBLANK(_StartUTC), _Result, _Duration)

 

I think something goes wrong. In sql all works fine as follow:

 

concat(floor(DATEDIFF(second, t.Start_UTC, t.End_UTC) / 86400), ' Day ',

        format_number(floor(DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400 / 3600), '00'), ':',

        format_number(floor((DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400) % 3600 / 60), '00'), ':',

        format_number(floor(((DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400) % 3600) % 60), '00'))             AS  `DurationGroup`,

Maybe I need to use different measure or structure.

Help appreciated 🙂

[Greg_Deckler]

@Hichamas4 Is this Excel? Can you provide an example of your source data as text? I don't think that CONCAT is a DAX function. You use & to concatenate in DAX.

[Hichamas4]

@Greg_Deckler  Thank you for your message.

In my message you can see two calculations, the first part is a dax measure I use for now, and the second part is SQL code I used in back-end.

The SQL code works fine but DAX code is not good yet.

[Greg_Deckler]

@Hichamas4 OK, but I don't have a sense of what your source data actually looks like. Can you post sample data as text? Then I can create a semantic model, test your DAX and figure out what is going haywire.

[Hichamas4]

@Greg_Deckler 

Sure. I have added a csv file with following colun:

SQLDateTime column is the result from SQL query.

concat(floor(DATEDIFF(second, t.Start_UTC, t.End_UTC) / 86400), ' Day ',

        format_number(floor(DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400 / 3600), '00'), ':',

        format_number(floor((DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400) % 3600 / 60), '00'), ':',

        format_number(floor(((DATEDIFF(second, t.Start_UTC, t.End_UTC) % 86400) % 3600) % 60), '00'))             AS  `DurationGroup`,

DAX duration column is result from DAX calculation.

VAR     _StartUTC       =   MIN('Order'[From_UTC])  

VAR     _EndUTC         =   MIN('Order'[To_UTC])

VAR     _EndUTCBlank =   NOW()

VAR     _Duration        =   MIN('Order'[DayTime])

VAR     _DateDiff   =   DATEDIFF(_StartUTC,  _EndUTCBlank, SECOND)

VAR     _Day        =   INT(_DateDiff/ 86400)

VAR     _Hour       =   FORMAT(INT(MOD(_DateDiff, 86400) / 3600), "#00")

VAR     _Minute     =   FORMAT(INT(MOD(MOD(_DateDiff, 86400), 3600) /60), "#00")

VAR     _Second     =   FORMAT(INT(MOD(MOD(MOD(_DateDiff, 86400), 3600), 60)), "#00")

VAR     _Result     =   _Day & " Day " & _Hour & ":" & _Minute & ":" & _Second

RETURN

IF(ISBLANK(_StartUTC), _Result, _Duration)

I hope this helps.

Table sample:

From_UTC	TO_UTC	SQLDayTime	DAX duration [Day, Time]
4/15/2014 5:45	11/26/2023 10:49	3512 Day 05:03:32	Day ::
4/10/2018 2:26	4/22/2020 12:11	743 Day 09:45:08	Day ::
8/30/2018 17:18	7/1/2020 10:00	670 Day 16:41:25	Day ::
2/18/2019 13:01	1/25/2020 14:47	341 Day 01:45:49	Day ::
4/27/2019 12:33	1/13/2020 14:46	261 Day 02:12:05	Day ::
5/23/2019 16:00	4/22/2020 12:11	334 Day 20:11:03	Day ::
5/23/2019 16:03	4/22/2020 12:03	334 Day 20:00:36	Day ::
5/23/2019 16:03	4/22/2020 12:06	334 Day 20:03:51	Day ::
5/23/2019 16:04	4/17/2020 7:46	329 Day 15:42:17	Day ::
5/23/2019 16:04	4/22/2020 12:06	334 Day 20:02:02	Day ::
5/23/2019 16:07	4/22/2020 12:03	334 Day 19:56:30	Day ::
6/2/2019 15:38	1/29/2020 15:24	240 Day 23:46:16	Day ::
8/7/2019 13:30	2/12/2020 8:55	188 Day 19:25:15	Day ::
8/19/2019 9:31	8/2/2020 12:00	349 Day 02:28:27	Day ::
9/9/2019 10:48	6/7/2021 8:14	636 Day 21:26:22	Day ::
9/10/2019 7:24	4/11/2020 7:03	213 Day 23:39:36	Day ::
9/20/2019 5:33	4/17/2020 8:51	210 Day 03:18:03	Day ::
9/24/2019 15:27	10/20/2020 14:35	391 Day 23:07:35	Day ::
9/27/2019 12:12	4/17/2020 11:51	202 Day 23:38:10	Day ::