cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Count of days with conditions

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.

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Count of days with conditions

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

282.PNG

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.

Highlighted
New Member

Re: Count of days with conditions

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.

Highlighted
Community Support
Community Support

Re: Count of days with conditions

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

300.PNG

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.

Highlighted
New Member

Re: Count of days with conditions

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 2 matrixMeasure 1 matrixMeasure 1 matrix

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors