Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Time difference between two date/times excluding weekends and holidays

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Sharma0815
Helper II
6 years ago

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

  • DataFriki's avatar
    DataFriki
    Advocate I
    1 year ago

    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)
)