topic Re: Count how many workdays that are from Min and Max value of the slicer in DAX Commands and Tips

Count how many workdays that are from Min and Max value of the slicer

Anonymous — Mon, 01 Nov 2021 12:12:38 GMT

Hi there,

I've been struggling with this one for a few days and wonder if someone can help.

I have a slicer with a date dimension "created_date", and I'm looking to get the count of how many working days are between the min and max value of the "created_date".

I've done two things:

Created a column with: WorkWeekDays = IF(WEEKDAY(CASES[Weekdays])>=6,FALSE,TRUE)
This works well: one the "weekdays" I have: Weekdays = WEEKDAY(CASES[created_date],2)
Created a CCountWorkDays" measure with The calculation below:

CountWorkDays =

VAR FirstDay = CALCULATE(MIN('CASES'[created_date]), ALLSELECTED('CASES'[created_date]))

VAR LASTDAY = CALCULATE(MAX('CASES'[created_date]), ALLSELECTED('CASES'[created_date]))

var Weekdays = COUNTROWS(FILTER(CASES, CASES[WorkWeekDays] = FALSE()))

RETURN

DATEDIFF(FirstDay,LASTDAY,DAY)

---

I realise I am not comparing it to the "Weekdays" var but haven't found a good way to do so.

Any ideas?

Re: Count how many workdays that are from Min and Max value of the slicer

Greg_Deckler — Mon, 01 Nov 2021 12:58:18 GMT

Anonymous Here is Net Work Days in DAX: Net Work Days - Microsoft Power BI Community

Re: Count how many workdays that are from Min and Max value of the slicer

Anonymous — Mon, 01 Nov 2021 14:01:32 GMT

Greg_Deckler you seem to have 2 different date dimensions in your example, does that changes things? I've tried it and couldn't make it work.

It seems that the RETURN function is what I'm doing wrong though, could I utilize something like this based on my previous example/variables?

COUNTX(FILTER( DATEDIFF(FirstDay,LASTDAY,DAY),Weekends=<6)

Re: Count how many workdays that are from Min and Max value of the slicer

Greg_Deckler — Mon, 01 Nov 2021 14:17:25 GMT

Anonymous Shouldn't matter. Why do you have DATEDIFF in there? You can't use DATEDIFF

Re: Count how many workdays that are from Min and Max value of the slicer

Anonymous — Mon, 01 Nov 2021 14:46:47 GMT

Greg_Deckler I've used your example but getting a return of thousands of days which is not right, should be 2 days instead.

Can you check what is wrong here?

NetWorkDays =

VAR Calendar1 = CALENDAR(MAX(CASES[created_time]),MAX(CASES[created_time]))

VAR Calendar2 = ADDCOLUMNS(CASES,"WeekDay_new",WEEKDAY(CASES[created_time],2))

RETURN COUNTX(FILTER(Calendar2,[WeekDay_new]<6),CASES[created_time])

Re: Count how many workdays that are from Min and Max value of the slicer

Greg_Deckler — Mon, 01 Nov 2021 15:31:33 GMT

Anonymous Sorry, but that's not even close. The original formula is:

NetWorkDays = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

So, making a ton of assumptions, seems like the translation to your data would be something like:

NetWorkDays = // The calendar needs to start at your minimum date and end at your maximum date in context VAR __Calendar1 = CALENDAR(MIN(CASES[created_time]),MAX(CASES[created_time])) VAR __Calendar2 = ADDCOLUMNS(__Calendar1,"__WeekDay",WEEKDAY([Date],2)) RETURN // this is an improvement on the original to use COUNTROWS instead of COUNTX COUNTROWS(FILTER(Calendar2,[__WeekDay]<6))

Re: Count how many workdays that are from Min and Max value of the slicer

Anonymous — Mon, 01 Nov 2021 16:00:04 GMT

Greg_Deckler I used your example and modified a few things but still getting over 30k results while it should be 3. Any ideas why? Thanks

NetWorkDays =

// The calendar needs to start at your minimum date and end at your maximum date in context

VAR __Calendar1 = CALENDAR(MIN(CASES[created_time]),MAX(CASES[created_time]))

VAR __Calendar2 = ADDCOLUMNS(__Calendar1,"__WeekDay",WEEKDAY(CASES[created_time],2))

RETURN

// this is an improvement on the original to use COUNTROWS instead of COUNTX

COUNTROWS(FILTER(__Calendar2,[__WeekDay]<6))

Re: Count how many workdays that are from Min and Max value of the slicer

Greg_Deckler — Mon, 01 Nov 2021 23:25:17 GMT

Anonymous I do not without sample data. The entire formula is predicated on what MIN date and MAX date you start with. You could change these into variables and return them in the RETURN statement to see what they are in context of whatever visual you are using. You have to pair the measure with something that is going to limit the context to what you are trying to figure out, like a case number or something. I am mildly concerned that you only have only a single column, you would generally have a "start" and "stop" time.

Let me go a big deeper into context, suppose you have the following table:

index	category	create_time
1	red	11/1/2021
2	red	11/2/2021
3	red	11/3/2021
4	blue	11/4/2021
5	blue	11/5/2021
6	blue	11/6/2021
7	green	11/7/2021
8	green	11/8/2021
9	green	11/9/2021
10	green	11/10/2021

Given that table, if you just use the measure as is, you would get 8 because the entire table is in context and there is a single weekend. If you used Index along with the measure, then every row would return 1 because the min and max dates would be the same except for 6 and 7 which would be 0. If you used category, red would return 3, blue 2 and green 3.

So, without any idea what your data looks like or how you are using the measure, I cannot possibly hope to answer your question about why you are getting 30,000 other than you aren't doing something correctly.

Re: Count how many workdays that are from Min and Max value of the slicer

v-cazheng-msft — Fri, 05 Nov 2021 05:33:17 GMT

Hi Anonymous

Please try this Measure.

CountWorkDays =

VAR FirstDay =

CALCULATE (

MIN ( 'CASES'[created_date] ),

ALLSELECTED ( 'CASES'[created_date] )

)

VAR LASTDAY =

CALCULATE (

MAX ( 'CASES'[created_date] ),

ALLSELECTED ( 'CASES'[created_date] )

)

VAR Weekdays =

CALCULATE (

COUNT ( 'CASES'[created_date] ),

FILTER (

'CASES',

'CASES'[created_date] >= FirstDay

&& 'CASES'[created_date] <= LASTDAY

&& 'CASES'[WorkWeekDays] = TRUE ()

)

RETURN

Weekdays

The result looks like this:

Also, attached the pbix file as a reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Count how many workdays that are from Min and Max value of the slicer

Anonymous — Fri, 05 Nov 2021 10:22:44 GMT

v-cazheng-msft First off, thank you so much for going through the trouble of creating a testfile with the right dimensions, really appreciate it!

I applied those and still noticed some bugs so downloaded your file and realized that the formula is not picking up the right values. See screenshot below, where I chose 7 total days (but only 5 business days) and we are getting incorrect values all around. Any ideas on how to fix the discrepancy?

Thank you!!

Re: Count how many workdays that are from Min and Max value of the slicer

Anonymous — Sat, 06 Nov 2021 13:08:40 GMT

v-cazheng-msft Greg_Deckler Any ideas on how to fix the gap on that testfile? Thanks

Re: Count how many workdays that are from Min and Max value of the slicer

v-cazheng-msft — Mon, 08 Nov 2021 02:06:32 GMT

Hi Anonymous

It’s related to the values in your created_date column. If you set the Slicer type as Between but not List, the values displayed in slicer are some consecutive dates by design. Based on the info from you, I guess this date column should contain some non-consecutive dates. And the count is based on this column, so the result will be the number of dates contained in this column and between the selected date range. If you would like to count the work days according to the date range in the slicer, please try this Measure:

CountWorkDays =

VAR FirstDay =

CALCULATE (

MIN ( 'CASES'[created_date] ),

ALLSELECTED ( 'CASES'[created_date] )

)

VAR LASTDAY =

CALCULATE (

MAX ( 'CASES'[created_date] ),

ALLSELECTED ( 'CASES'[created_date] )

)

VAR calendar_ =

CALENDAR ( FirstDay, LASTDAY )

VAR work_cal =

ADDCOLUMNS ( calendar_, "work", WEEKDAY ( [Date], 2 ) )

RETURN

COUNTAX ( FILTER ( work_cal, [work] < 6 ), [Date] )

The the result will look like this:

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

Re: Count how many workdays that are from Min and Max value of the slicer

v-cazheng-msft — Thu, 11 Nov 2021 07:30:20 GMT

Hi Anonymous

May I know whether the newly posted solution helps you get the result you want? If it helps, could you please kindly Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks in advance!

Best Regards,

Community Support Team _ Caiyun