Calculate Days between two time stamps without weekends or holidays

[Anonymous]

Hi,

I want to calculate the number of days between two timestamps but exclude Saturday, Sunday and holidays in the result.   So these are incoming requests to a support desk and then the response.

I found this link: http://www.powerpivot-info.com/post/143-q-how-can-i-calculate-difference-between-two-dates-in-dax-se... but I'm not sure how to apply it here or use that in Power BI or ignore weekends and holidays in the calculations.  In the example above on the first line the day measurement in our estimation is five point something days  (I know really accurate ) .  We measure from the time part to the next time part as well as the date if that makes any sense rather than just from one day to 00:00 the next day.

I'm also thinking we might try to calculate it even more accurately because people allocate items to support in the evening but it doesn't get looked at until we are operational the next working day.  I might be really over complicating this though!

Would anyone have any idea if this is possible and if so how to achieve this?

It's something we've wanted to be able to do in this report for a long time and I think we're getting close.

Thank you.

[MattAllington · Accepted solution]

I just answered a very similar question here. http://community.powerbi.com/t5/Desktop/No-worked-days-by-month/m-p/87975#U87975

If this was me, I would split the date and time into 2 columns for each.  Then add a work day column in the calendar table (1 for a workday, 0 for a non work day).  Then write a formula like the one I linked to above. to get the count of days.  You can just do a straight calc on the time stamps and add the result to the days 

Days =CALCULATE(sum('Calendar'[Work Day]),
FILTER('Calendar',
'Calendar'[Date] >= FIRSTDATE(Data[Date_StopWork]) &&
'Calendar'[Date] < LASTDATE(Data[Date_Returned])
)
)

remove the relationship between your calendar table and the data table (hopefully that doesn't break anything else)

Time = sumx(data, data[end time] - data[start time]  )

Combined = [Days] + [Time]

Something like that.  You may need to tweak the formulas

[Anonymous]

Hi,

I got an error on this but I'm not using the right formula on my calendar table.

Sorry, this is beginner stuff but how do I add the workday 1, 0 column to the date table?  Can I incorporate the holidays somehow too by referring to the holiday table so that will give it a 1 or 0 result in calendar?

Many thanks

[KHorseman]

You could adapt the method I used in this thread. Obviously the measure itself is doing something different from what you want here but the method of creating a temporary date table and filtering out the weekends is applicable to your problem.

[Anonymous]

Hi,

@MattAllington I managed to get the weekday/hols thing to work with the calendar in this measure:

WEEKD = IF(OR([Weekdayg]=7,[Weekdayg]=1),0,IF([Holiday]<>"Not Holiday",0,1))

and I used your filter as you described above.  I think we're going to leave the hours and it some cases it doesn't make sense:

The top one isn't a day and six hours but it's a whole lot better than we had before!

Thank you @MattAllington and @KHorseman for your help with this, we've been wanting this for some time.