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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dcs136
Advocate I
Advocate I

Calculate Date and Time difference considering the weekends and workhours

  Hi everybody!

 

I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer!

Here is the thing,

 

I need to calculate the difference between dates and time, but the thing is that I need to calculate just the working days and the workhours.

Sem título.jpg

Taking the first line as an example: (03/11/2016 13:57:22 - 03/01/201613:36:38) the column hours should be 63:38:38

Considering that 03/05 and 06/05 are saturday and sunday, and considering that the work hours are from 08:00AM to 18:00PM.

 

How can I solve that!

 

Thank you!

2 ACCEPTED SOLUTIONS

@dcs136

 

The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

 

Calculate Date and Time difference considering the weekends and workhours_1.jpg

 

Best Regards,

Herbert

 

View solution in original post

@dcs136

 

How about the result if we update the “FirstDaySecDiff” measure as below?

 

FirstDaySecDiff = 
IF (
    Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
        && Table1[FirstDayEndTime] <= Table1[DateTimeTo],
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
    IF (
        Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
            && Table1[FirstDayEndTime] > Table1[DateTimeTo],
        DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
        0
    )
)

 

Best Regards,

Herbert

View solution in original post

43 REPLIES 43

Hello Herbert,

 

I need a little help here again.

 

I tried to solve it but I couldn't find an solution.

 

As you can see on the picture bellow, the calculation between two dates is correclty just when the date is different. But when the difference is just between hours the result is the DateTimFrom column - FirstDayEndTime Column.

 

For example:

03/01/2016 10:50:42  = 7,155 - but the result should be 2,046

 

 Test.jpg

Hebert!

 

Thanks again!

 

It was exactly what you said.

 

The column Workday was duplicate.

 

Now it's working perfect!!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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