Calculate number of working days between the first date in slicer and today

[Anonymous]

Hi, I want to create a card visual that shows the number of working dates between the first date in slicer and today (similar to networkdays in excel). 

For example, the first date in my slicer here is 1st Feb 2021. I would like to calculate the number of working days between 1st Feb 2021 and today.  I have a isWorkingDay column that returns TRUE for weekdays and FALSE for weekends. I tried to calculate the total number of working days by creating a column in my date table with this formula 

• total working days =CALCULATE(COUNTROWS('Calendar'),DATESBETWEEN('Calendar'[Date],'Calendar'[Date],TODAY()),'Calendar'[isWorkingDay] = TRUE, ALLSELECTED('Full Data'))+1

and it returns 128 for 1st Feb 2021 to 28th July 2021. However, when I dragged the column to my visual, it returns 3720 instead of 128. How can I fix this? 

Thanks in advance! 

[Anonymous]

Thanks for the answer! I was actually looking for the working days between 1st Feb 2021 and the current date (28th July 2021) so I changed VAR _MaxDate = MAX([Date]) to VAR _MaxDate = TODAY(). However, does VAR _minDate change according to the date on my slicer? 

[Anonymous]

Yup the slicer is limiting the range of dates that are available when the measure is being calculated so MIN([date]) will be the earliest date that is visible.

Also just as a side note, if you have a True/False column and you want to test for True, you don't need to write [col1] = True, you just need to put the column name e.g. FILTER (Table, [col]) == FILTER (Table, [col] = True) and  FILTER(Table, NOT([col)) == FILTER (Table, [col] = False)).

[Anonymous · Accepted solution]

Your total working days is calculating the up to today.  You'll need to change the calculation first so that it takes the min visible day (1/2/2021) and the max visible day (4/3/2021) and calculates the working days between these two variables.

If you change the True/False to 1/0 you can use a simple SUM like below:

Working Days =

VAR _minDate = MIN([Date])

VAR _MaxDate = MAX([Date])

RETURN

CALCULATE(

SUM(Dates[isWorkingDay]),

DATESBETWEEN(Dates[Date],_minDate,_MaxDate)

)

Also depending on what you're using this report for, don't forget to include public holidays in your working day column! Good luck