Time difference between two date/times excluding weekends and holidays

[Anonymous]

Hello Power BI experts!

I am looking for help to add a column that calculates the # of hours or days (not whole) between two date/times.  I have included below a sample of the date/times I have and also have work days marked with a 1 and weekends and holidays with a 0 in my calendar.  Either hours or days would work as I can calcualte hours back to days but whole days will not work.

I'm sorry if this has been answered before as I did search and tried several ways but have nothing seemed to work or fit for me  and I am getting myself confused 😞 .  Any help on how to do this would be greatly appreciated.

Thank you!

Samples of my dates:

Date/Time Opened	Date/Time First Resolved
9/26/2018 11:44	9/26/2018 14:47
9/9/2018 14:37	9/18/2018 8:55
8/8/2018 9:06	9/7/2018 10:24
9/10/2018 14:41	9/10/2018 15:11
9/9/2018 14:37	9/18/2018 8:55
9/26/2018 8:28	9/26/2018 9:22

[Anonymous · Accepted solution]

@Anonymous,

You can create a calculated column using DAX below.

Total Working Days Column = 
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= Table[Date/Time Opened]
            && 'Calendar'[Date] <= Table[Date/Time First Resolved]
    ),
    'Calendar'[isWorkDay]
)

However, if you need to calculate work hours between two date time valyues, please check the Power Query function in this similar thread: https://social.technet.microsoft.com/Forums/en-US/f3fc729e-a2ec-409a-b20e-b5cf8ff5dc2a/calculate-work-hours-between-two-timedate-stamps?forum=powerquery.

Regards,
Lydia

[Sharma0815]

@Anonymous  did you created any calendar table,if yes - can you please share the DAX

[DataFriki]

Yes, you need to create a calendar table:

Calendar = ADDCOLUMNS(
    CALENDAR(DATE(2018, 1, 1), DATE(2018, 12, 31)),
    "IsWorkDay", IF(WEEKDAY([Date], 2) < 6, 1, 0)
)

[Krishna1992]

hi,

I am working on the same thing. I have taken workdays as 1 and weekend and Holidays as 0, used mentioned DAX, but I am getting right for weekends and holidays as right value, but for weekdays +1 is added to the value which is not accpetable. 

Ex. Weekday dates

Start date 14-11-2019   

End date 16-11-2019

Difference should be 2 days but it is taking as 3 days, because it is summing from workday which is marked as 1 for weekdays. I am not sure where I am making an error.

Please let me know how to overcome this.

[Destroyer_]

Plz share DAX code I am also facing same issue . please help