Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a table that looks like this:
I need to calculate the hours between the created on/at and the finalized on/at.
We count all hours that occur, 24/7 except for weekends and significant holidays.
I've tried alot but i cant find a solution to disregard the weekends and holidays...
Anyone have any advice on how to tackle this?
/ Johannes
Solved! Go to Solution.
Hi @tex628
I tried recreating your scenario and calculating no of days excluding weekend days.
So I created a date dimention and with weeknum function got saturdays (6) and sundays(7) in a new column in date dimention.
And in my start and end date table first calculated datediff in no of days
and then no of weekend days in between these two days by creating new column using formula as
Weekend Days =
var _stdate = Working[Date 1]
var _enddate = Working[Date 2]
return
CALCULATE(COUNT('Date Dim'[Date]),FILTER('Date Dim','Date Dim'[Day Name] =6 || 'Date Dim'[Day Name] = 7),FILTER('Date Dim','Date Dim'[Date]>= _stdate && 'Date Dim'[Date] <= _enddate))
And lastly subtracting both
Thanks
Vinayak Kohli
Hi @tex628
I tried recreating your scenario and calculating no of days excluding weekend days.
So I created a date dimention and with weeknum function got saturdays (6) and sundays(7) in a new column in date dimention.
And in my start and end date table first calculated datediff in no of days
and then no of weekend days in between these two days by creating new column using formula as
Weekend Days =
var _stdate = Working[Date 1]
var _enddate = Working[Date 2]
return
CALCULATE(COUNT('Date Dim'[Date]),FILTER('Date Dim','Date Dim'[Day Name] =6 || 'Date Dim'[Day Name] = 7),FILTER('Date Dim','Date Dim'[Date]>= _stdate && 'Date Dim'[Date] <= _enddate))
And lastly subtracting both
Thanks
Vinayak Kohli
Thanks for your response!
I'm looking for an exact value in hours and not days. This is the baseline for case-handling statistics, the amount of time is often lower than a day.
Br,
Johannes
Hi @tex628
Take the difference in hours and multiply the number of weekend days with 24 and subtract them.
for finding time difference you need to concat date and time together.
My excuse will be that it has been a long day!
Your solution works very well. Thank you for the assistance!
Br,
J
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |