Measure with multiple filters to count working days

[frankhofmans]

hi PI experts,

I have a table with a start_date and a end_date of cases (sickness cases) and i would like to calculate the working days between the start_date and the end_date, but if the start date is earlier than the first date of the selected period, than i need the number of working days between the first date of the selected periode and de end_date. The same goes for the end_date, so if the end_date is later than the selected period, i need the number of working days between the start_date (or the first date of the selected period if the start_date is earlier than the first date of the selected period) and the end_date. So i have four possibles outcomes:

Start_Date	End_Date	Status	Calculation needed
30 March 2020	05 July 2020	Started before Q2, ended after Q2	# of working days between first day Q2 and last day Q2
02 April 2020	10 April 2020	Started in Q2, ended in Q2	# of working days between Start_Date and End_Date
05 April 2020	10 August 2020	Started in Q2, ended after Q2	# of working days between Start_Date and last day Q2
01 March 2020	01 May 2020	Started before Q2, ended in Q2	# of working days between first day Q2 and End_Date

The result i want is:

Q	# of cases	# of working days total	Average
Q1	(al cases handled in Q1, so cases with Date_start in Q1 (ended in or after Q1) and cases with Date_start before Q1 and Date_end in or after Q1	Sum of working days, given the "calculation needed" possible outcomes.	# of working days total / # of cases
Q2
Q3
Q4

I have no problem with the Excel formula, but i can't replicate it in Power BI. Can someone help me with this (for me) impossible calculation? 🙂 Thanks in advance!!

[v-lid-msft · Accepted solution]

Hi @frankhofmans ,

Pleas change the " WEEKDAY ( [Date], 3 ) <= 5 " in measure “Calculation needed” to "WEEKDAY ( [Date], 3 ) <= 4":

Calculation needed = 
VAR StartDate =
    MAX ( MIN ( 'Calenda'[Date] ), MIN ( 'Outcomes'[Start_Date] ) )
VAR EndDate =
    MIN ( MAX ( 'Calenda'[Date] ), MAX ( 'Outcomes'[End_Date] ) )
RETURN
    IF (
        StartDate <= EndDate,
        COUNTROWS (
            FILTER ( CALENDAR ( StartDate, EndDate ), WEEKDAY ( [Date], 3 ) <= 4 )
        ),
        0
    )

Best regards,

[v-lid-msft]

Hi @frankhofmans ,

We can try to use following measures to meet your requirement:

Calculation needed = 
VAR StartDate =
    MAX ( MIN ( 'Calenda'[Date] ), MIN ( 'Outcomes'[Start_Date] ) )
VAR EndDate =
    MIN ( MAX ( 'Calenda'[Date] ), MAX ( 'Outcomes'[End_Date] ) )
RETURN
    IF (
        StartDate <= EndDate,
        COUNTROWS (
            FILTER ( CALENDAR ( StartDate, EndDate ), WEEKDAY ( [Date], 3 ) <= 5 )
        ),
        0
    )

# of cases = 
CALCULATE (
    COUNTROWS ( 'Outcomes' ),
    FILTER (
        'Outcomes',
        OR (
            AND (
                'Outcomes'[Start_Date] <= MAX ( 'Calenda'[Date] ),
                'Outcomes'[Start_Date] >= MIN ( 'Calenda'[Date] )
            ),
            AND (
                'Outcomes'[Start_Date] < MIN ( 'Calenda'[Date] ),
                'Outcomes'[End_Date] >= MIN ( 'Calenda'[Date] )
            )
        )
    )
)

# of working days total = 
SUMX (
    FILTER (
        'Outcomes',
        OR (
            AND (
                'Outcomes'[Start_Date] <= MAX ( 'Calenda'[Date] ),
                'Outcomes'[Start_Date] >= MIN ( 'Calenda'[Date] )
            ),
            AND (
                'Outcomes'[Start_Date] < MIN ( 'Calenda'[Date] ),
                'Outcomes'[End_Date] >= MIN ( 'Calenda'[Date] )
            )
        )
    ),
    [Calculation needed]
)

Average = DIVIDE([# of working days total],[# of cases],0)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？

By the way, PBIX file as attached.

Best regards,

[frankhofmans]

Dear Dong Li,

a few month ago you helpend me with a questions about the working days. I want to replicate your measure, but this time for calendar days instead of working days. How do i change the following measure to create the calendar days?

Calculation needed =
VAR StartDate =
    MAX ( MIN ( 'Calenda'[Date] ), MIN ( 'Outcomes'[Start_Date] ) )
VAR EndDate =
    MIN ( MAX ( 'Calenda'[Date] ), MAX ( 'Outcomes'[End_Date] ) )
RETURN
    IF (
        StartDate <= EndDate,
        COUNTROWS (
            FILTER ( CALENDAR ( StartDate, EndDate ), WEEKDAY ( [Date], 3 ) <= 5 )
        ),
        0

thanks in advance,

Regards,

Frank
    )

[frankhofmans]

Dear Dong Li,

thanks for your response. I'm getting really close. I created the following test table:

Start_date	End_date	Start date Q1 needed	End date Q1 needed	Start Date Q2 needed	End date Q2 needed	# Working days (working days between Start_Date and End_Date)	# Working days Q1 needed (working days between start date Q1 and end date Q1)	# Working days Q1 needed (working days between start date Q2 and end date Q2)
5-1-2019	28-2-2020	1-1-2020	28-2-2020			300	43	0
31-12-2019	9-1-2020	1-1-2020	9-1-2020			8	7	0
1-12-2019	1-4-2020	1-1-2020	31-3-2020	1-4-2020	1-4-2020	88	65	1
7-1-2020	20-1-2020	7-1-2020	20-1-2020			10	10	0
7-1-2020	1-7-2020	7-1-2020	31-3-2020	1-4-2020	30-6-2020	127	61	65
20-12-2019	30-4-2020	1-1-2020	31-3-2020	1-4-2020	30-4-2020	95	65	22
1-5-2020	3-5-2020			1-5-2020	3-5-2020	1	0	1
1-5-2020	10-8-2020			1-5-2020	30-6-2020	72	0	43
1-3-2020	1-6-2020	1-3-2020	31-3-2020	1-4-2020	1-6-2020	66	22	44

The outcome has to be:

Q	# Cases	Sum working days
Q1	7	273	sum working days Q1 needed
Q2	6	176	sum working days Q2 needed

Based on your suggestions, which i copied exactly, i got the following results:

Q	# cases	Sum working days
Q1	7	326
Q2	6	212

The # of cases is correct, but i got a difference in the sum of working days (and the difference is not explained by calendar days vs working days). Do you any other suggestions?

thanks in advance!

Regards, Frank

[v-lid-msft · Accepted solution]

Hi @frankhofmans ,

Pleas change the " WEEKDAY ( [Date], 3 ) <= 5 " in measure “Calculation needed” to "WEEKDAY ( [Date], 3 ) <= 4":

Calculation needed = 
VAR StartDate =
    MAX ( MIN ( 'Calenda'[Date] ), MIN ( 'Outcomes'[Start_Date] ) )
VAR EndDate =
    MIN ( MAX ( 'Calenda'[Date] ), MAX ( 'Outcomes'[End_Date] ) )
RETURN
    IF (
        StartDate <= EndDate,
        COUNTROWS (
            FILTER ( CALENDAR ( StartDate, EndDate ), WEEKDAY ( [Date], 3 ) <= 4 )
        ),
        0
    )

Best regards,

[frankhofmans]

Dear Dong Li,

A month ago you helped me with a problem. Maybe you can help me one more time. i want to present the number of open sickness (started before the last day of a month or week, closed after the last day of the month or week) and the number of days those sickess are open (the number of working days between the startdate of the sickness en the last day of the month or week).

Startdate Sickness Case	Enddate Sickness Case	Open on 31th january 2020	Open on 29th february 2020	Open on 31th march 2020	# Working days on 31th january 2020	# Working days on 29th february 2020	# Working days on 30th march 2020
1-1-2020	3-2-2020	Y	N	N	23 (working days between start date and 31th of january)	0	0
10-1-2020	20-1-2020	N	N	N	0	0	0
11-1-2020	7-4-2020	Y	Y	Y	15	35	56
3-2-2020	10-2-2020	N	N	N	0	0	0
7-2-2020	3-3-2020	N	Y	N	0	16	0
5-3-2020	20-3-2020	N	N	N	0	0	0
5-3-2020	4-4-2020	N	N	Y	0	0	18

I solved the first part of my wish (the number of open cases in a certain period), but i can't find a way to present the number of working days between the start date and the last date of the period. The way i want to present it is:

	Jan	Feb	March
# of open sickness cases	2	2	2
# of open sickness cases 0-2 days	0	0	0
# of open sickness cases 3-10 days	0	0	0
# of open sickness cases 11-20 days	1	1	1
# of open sickness cases > 20 days	1	1	1

thanks in advance for your help!

Regards,

Frank

[v-lid-msft · Accepted solution]

Hi @frankhofmans ,

Firstly, we need to create a row table:

Then we create a calendar table as column

CalendarTable = ADDCOLUMNS(CALENDARAUTO(),"MonthName",FORMAT([Date],"MMM"),"MonthNo",MONTH([Date]),"Year",YEAR([Date]))

 Then we can create a measure to meet your requirement:

Count = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Case ID] ),
    FILTER (
        'Table',
        'Table'[Startdate Sickness Case] <= MAX ( 'CalendarTable'[Date] )
            && 'Table'[Enddate Sickness Case] >= MAX ( 'CalendarTable'[Date] )
            &&
            VAR workday =
                COUNTROWS (
                    FILTER (
                        CALENDAR ( 'Table'[Startdate Sickness Case], MAX ( 'CalendarTable'[Date] ) ),
                        WEEKDAY ( [Date], 2 ) <= 5
                    )
                )
            RETURN
                workday >= MIN ( 'Row Table'[MinDay] )
            && workday <= MAX ( 'Row Table'[MaxDay] )
    )
) + 0

By the way, PBIX file as attached.

Best regards,

[frankhofmans]

It works perfect! thanks again for the quick response!

Regards,

Frank

[frankhofmans]

Can i ask you one more question? what is the dax formule if i want the cumulated count of days. so every quarter start with 1st january of the selected year. 

Start date	End Date	Outcome needed
01-11-2019	10-1-2020	Working days between 1-1-2020 and 10-1-2020
02-01-2020	26-02-2020	Working days between 02-01-2020 and 26-02-2020
10-02-2020	05-04-2020	Working days between 10-02-2020 and 31-3-2020

Thanks in advance!

[v-lid-msft]

Hi @frankhofmans ,

Of couse you can ask question here, we will try to solve it. Do you mean set filter Year=2020, then the every record which start date earlier before 2020 will calculated from 01-01-2020? It seems the previous formula can output the desire result.

Best regards,

[frankhofmans]

hi,

i added a extra column in my date table (column name "first date of year" with 1st januar for every year). i changed the min(date) in min(first date of year) and now its working. i simple solution, i had to think longer before asking you the question. My apologies...

[frankhofmans]

Wow, a perfect outcome! you are my hero 🙂 thanks for the help!

[Greg_Deckler]

Net Work Days Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

[frankhofmans]

hi Greg,

thanks for your reply. For me the difficulty is to combine the networking days function (as you described in your reply) with a multipe arguments calculation (more than 2 arguments):

if(and(start_date >= first day Q1;start_date<=last day Q1;end_date<=last day);working days between start_date and end_date

if(and(start_date < first day Q1; end_date>=first day Q1; end_date<=last day Q1);working days between first day Q1 and end_date

etc.

Do you have a solutions for me to calculate this in 1 Dax formula?

Thanks!