calculate weekdays basis user selection

[Gopinath_iyer]

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

[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] ?

[johnt75]

You can use

Num week days = NETWORKDAYS( MIN('Date'[Date]), MAX('Date'[Date]))

[Gopinath_iyer]

Thanks 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