Count of days with conditions

[dcat]

Hello All,

I need to count the number of working days based on a Monday through Friday work week that a sample was received. 

I have a "sample received" date and a "test complete" date, however I need a running count of working days the sample is in test until the test complete date is entered. End resultes would be the total number of working days the sample was in test or currently in test.

[dax]

Hi dcat,

Below is my design:

My sample

task	received	complete
1	8/1/2019	8/15/2019
2	7/20/2019	8/3/2019
3	8/5/2019	8/25/2019

create a calendat table like below

cal = CALENDAR(DATE(2019,7,1),DATE(2019,8,31))

then create a meausre like below(I use monday as 1, sunday as 7)

Measure 2 =
CALCULATE (
    COUNT ( cal[Date] ),
    FILTER (
        ALL ( cal ),
        cal[Date] >= MIN ( workdays[received] )
            && cal[Date] <= MIN ( workdays[complete] )
            && WEEKDAY ( ( cal[Date] ), 2 ) <> 6
            && WEEKDAY ( ( cal[Date] ), 2 ) <> 7
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcat]

This worked perfectly for samples with a testing complete date entered. I also had the condition if there was no testing complete date entered than it would keep a count of the working days from when the sample was received. I really appreciate you help so far.

[dax]

Hi dcat, 

You could try below measure(in this condition, receive date <=today())

Measure 2 =
CALCULATE (
    COUNT ( cal[Date] ),
    FILTER (
        ALL ( cal ),
        cal[Date] >= MIN ( workdays[received] )
            && cal[Date]
                <= IF (
                    ISBLANK ( MIN ( workdays[complete] ) ),
                    TODAY (),
                    MIN ( workdays[complete] )
                )
            && WEEKDAY ( ( cal[Date] ), 2 ) <> 6
            && WEEKDAY ( ( cal[Date] ), 2 ) <> 7
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcat]

Measure 2 indeed added the condition and works great, however it pulls all samples into my matrix. Included are images of measure 1 used in the matrix which has the format needed but not the days in test for samples not yet completed. Measure 2 gives all the correct data but counts all samples in the matrix. Measure 2 matrixMeasure 1 matrix