Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hemas-naidu
Frequent Visitor

Calculate Time Difference between 2 cross days

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.

hemasnaidu_1-1721878112794.png

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 ?

 

hemasnaidu_2-1721878415999.png

Thank you

 

1 ACCEPTED SOLUTION
mickey64
Super User
Super User

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

mickey64_0-1721912676338.png

 

Step 1: I duplicate the 'Duration_Mins' column.

mickey64_1-1721912773966.png

 

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.

mickey64_2-1721913043865.png

 

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

mickey64_3-1721913140951.png

 

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

mickey64_4-1721913287212.png

 

Step 7: I add the 'Flag' column.

mickey64_5-1721913364591.png

 

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

- Before -

mickey64_6-1721913489205.png

- After -

mickey64_7-1721913514122.png

 

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)))
mickey64_8-1721913613150.png

 - Matrix -

mickey64_9-1721913638614.png

 

 

View solution in original post

3 REPLIES 3
mickey64
Super User
Super User

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

mickey64_0-1721912676338.png

 

Step 1: I duplicate the 'Duration_Mins' column.

mickey64_1-1721912773966.png

 

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.

mickey64_2-1721913043865.png

 

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

mickey64_3-1721913140951.png

 

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

mickey64_4-1721913287212.png

 

Step 7: I add the 'Flag' column.

mickey64_5-1721913364591.png

 

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

- Before -

mickey64_6-1721913489205.png

- After -

mickey64_7-1721913514122.png

 

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)))
mickey64_8-1721913613150.png

 - Matrix -

mickey64_9-1721913638614.png

 

 

I will try and update you soon.. thanks

RossEdwards
Solution Sage
Solution Sage

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.