cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gandalf00
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). 

 

gandalf00_2-1627460638087.png

 

 

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! 

1 ACCEPTED SOLUTION
hasrya
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

View solution in original post

3 REPLIES 3
gandalf00
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? 

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

hasrya
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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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