Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dcat
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
dax
Community Support
Community Support

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.

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
Community Support
Community Support

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.