cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
Regular Visitor

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

3 REPLIES 3
Frequent Visitor

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?

Regular Visitor

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)).

Regular Visitor

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors