Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |