Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.