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

[cmd105]

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:

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

[v-cazheng-msft]

Hi @cmd105 

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!

[cmd105]

@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!!

[cmd105]

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

[v-cazheng-msft]

Hi @cmd105 

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

[v-cazheng-msft · Accepted solution]

Hi @cmd105 

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

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!

[Greg_Deckler]

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

[cmd105]

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

[Greg_Deckler]

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

[cmd105]

@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])

[Greg_Deckler]

@cmd105 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))

[cmd105]

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

[Greg_Deckler]

@cmd105 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.