Forum Discussion

dcs136's avatar
dcs136
Icon for Advocate I rankAdvocate I
10 years ago
Solved

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 tim...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    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.

     

     

    Best Regards,

    Herbert

     

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

Rhndy's avatar
Rhndy
Frequent Visitor
8 years ago

Hi maartjedutchy,

 

I've tried your solution but it doesn't work properly. 

Service window =  Workdays from 07:30 to 17:00 (NL-time)

 

I have the following item:

Date/Time ticket created = 6/7/2018 on 9:31 

Date/Time ticket closed = 11/7 on 11:22

 

Calculation by hand:

6/7 - 7 hours, 29 minutes (9:31 - 17:00)

7/7 - 0 hours

8/7 - 0 hours

9/7 - 9 hours, 30 minutes (7:30-17:00)

10/7 - 9 hours, 30 minutes (7:30-17:00)

11/7 - 3 hours, 52 minutes  (7:30-11:22)

Total is 30 hours,  21minutes or 1821 minutes

 

Your calculation give me a TotalMinDiff of 1432. See attached picture.

Something seems off, any idea?

 

 

 

 

Rhndy's avatar
Rhndy
Frequent Visitor
8 years ago

i think i found out what's wrong here:

- The middayssecdiff has the servicehours hard-coded (3600 * 10). My window is 9,5 hours, by changing it to 3600 * 9.5 that was allright and got a small deviation fixed.

 

- The formula for FirstDaySecDiff produces 0 when the ticket hasn't been resolved the same day. That's wrong.

The formula in your file:

IF (
'SLA'[FirstDayEndTime] >= 'SLA'[DateTimeFrom]
&& 'SLA'[FirstDayEndTime] <= 'SLA'[DateTimeTo] && RELATED('Calendar'[WorkDay]) = TRUE ();
DATEDIFF ( 'SLA'[DateTimeFrom]; 'SLA'[FirstDayEndTime]; SECOND );

 

Fails on the RELATED('Calendar'[WorkDay]) = TRUE ();

When turning that off, it works. But it doesn't when a ticket got created in the weekend. As we speak i'm checking to fix that.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I changed a view things to work with regionalization a little bit easier.

     

    In the time calculations dont use & "18:00:00" because then PowerBI has to interpret a text to a date which is depending on the regional settings. Instead just add the time as datetime object like so: + TIME(18,0,0).

    When you do it like that you will always receive an datetime object and its easy (and propably faster) to interpret for PowerBI.

     

    I also used a public holiday calendar and imported it to another table and created a * to Many relationship between the calendar.date and the hollidays.date.  My Calculated Column in Calendar now checks if it was a holiday:

    Holliday = CALCULATE( MIN(Hollidays[HollidayName]), ALLEXCEPT('Calendar','Calendar'[Date]))

    and then you can check if ( isblank(holliday)) which will give you if the date was a holiday or not. I added to the workday check if the day was a holliday or not.