Forum Discussion
Helper IIcalculate weekdays basis user selection
Hi All,
I want to calculate weekdays (mon to fri) basis user selection.
I have a date column which will used in slicer and basis the date range user selects in slicer i want to calculate the number of weekdays.
can this be achieved ?
regards,
Gopi
5 Replies
johnt75Super User
You can use
Num week days = NETWORKDAYS( MIN('Date'[Date]), MAX('Date'[Date]))
Gopinath_iyerThanks for the reply, i have a follow up query...will try to explain with an example.
my data has timesheet entries of employees basically each emp needs to book 8 hours every day, so if an emp has booked hours for all the days in the time range selected in the slicer then the above formula works fine. But if an emp has not booked hours for some days then it is not coming correct. e.g if there are 20 working days as per the date range selected in slicer and X resource has booked 8 hours a day for 18 days then my result should be 90% [(18*8)/(20*8)] but i am getting 100% because the above calculation is excluding 2 days as there is no entry in data
- johnt75
Add an "Is working day" column to your date table, which returns 1 if it is Mon-Fri, otherwise 0. Then you should be able to do something like
Num days worked = VAR startDate = MIN ( 'Slicer Date'[Date] ) VAR endDate = MAX ( 'Slicer Date'[Date] ) RETURN CALCULATE ( SUM ( 'Date'[Is working day] ), DATESBETWEEN ( 'Date'[Date], startDate, endDate ), TREATAS ( VALUES ( 'Timesheets'[Date] ), 'Date'[Date] ) )- Gopinath_iyer
Hi
I created a date table using calanderauto() functions and used the above suggestion and result i am getting is 260, but i want the weekdays between the dates selected in the slicer
- johnt75
Are you using the same column on your date slicer as you have in the code where I put 'Slicer Date'[Date] ?