Calculate Time Difference between 2 cross days

[hemas-naidu]

Hi guys,

Intention is to have the Diff hours based on Date cut off

Need help to calculate the following Duration_Mins. Currently at Row 5. the StartTimeStamp (17th Jul 23:17) and EndTimeStamp (18th 02:09) goes over to next day. Therefore the Duration Mins calculated based on StartTimeStamp does not return the correct value based on Date cut off due to cross days.

Total Hours for 17th Jul is 26:09 hours where 18th Jul is 21:50 hours.

One wish list is to have as below, but i have millions of records in table. 

But how to implement in Power BI? I m not able to change the data ingestion at table. Only allowed to do changes at Power BI level.

Or is there any better method to implement ?

Thank you

[mickey64 · Accepted solution]

Step 0: I use these data and calculate by 'Minutes'.

Step 1: I duplicate the 'Duration_Mins' column.

Step 2: I chande the type of 'Duration_Mins - Copy' column.

    'Duration_Mins - Copy' column: 'Time' -->  'Decimal Number'

Step 3: I rename the 'Duration_Mins - Copy' column.

    'Duration_Mins - Copy' --> 'Duration_Day'

Step 4: I add a 'Duration_Min' column and change the type of the column.

Step 5: I add a 'Day' column and rename it. ('Day' --> 'S_Day')

Step 6: I add a 'Day' column and rename it. ('Day' --> 'E_Day')

Step 7: I add the 'Flag' column.

Step 8: I unpivot 'StartTimeStamp' and 'EndTimeStamp' columns.

- Before -

- After -

Step 8: I add a column and make a matrix.

Cal_Duration_min = IF([Flag]="Divide" && [Attribute]="StartTimeStamp",DATEDIFF([Value],DATE(YEAR([Value]),MONTH([Value]),[E_Day]),MINUTE),IF([Flag]="Divide" && [Attribute]="EndTimeStamp",DATEDIFF(DATE(YEAR([Value]),MONTH([Value]),[E_Day]),[Value],MINUTE),IF([Attribute]="StartTimeStamp",1440*[Duration_Mins],0)))

 - Matrix -

[hemas-naidu]

I will try and update you soon.. thanks

[RossEdwards]

You could use the Duration functions in Power Query?  As part of your import, create a new column which uses Duration.Minutes.  Would look something like:  "Duration.Minutes([EndTimeStamp] - [StartTimeStamp])

Duration functions - PowerQuery M | Microsoft Learn