Calculate # of cases closed within the same week

[lilych]

I need to create a report that calculates the count of cases that were open and closed in the same calendar week. Some cases are still open and do not have a close date. See sample data below. How would I go about this? 

Thanks in advance!

Case Id	Created Date	Closed Date
1	3/23/21	3/25/21
2	3/24/21	3/24/21
3	3/26/21	3/29/21
4	4/1/21	4/6/21
5	4/6/21
6	4/12/21	4/19/21

[jdbuchanan71 · Accepted solution]

@lilych 
Something along these lines should also handle the weeks that cross years correctly and avoid nested iterators which could cause performace problems.

Same Week Cases = 
SUMX (
    'Cases',
    INT ( DATEDIFF ( 'Cases'[Created Date], 'Cases'[Closed Date], WEEK ) == 0 )
)

DATEDIFF WEEK just counts the number of sundays that have been crossed since the first date so

• If a case starts on a Sat and ends the next day will not be counted because it crossed a Sun after the first date. 
• If a case starts on Sun and ends on the following Sat it will count, no Sun were crossed after the first date. 

The INT ( ) just turns True/False into 1/0.  

==0 checks if the DATEDIFF is exactly 0 which avoids counting cases with no closed date incorrectly.

[Anonymous]

@lilych 

Please do not use @Fowmy's measure as it's incorrect. Here's a version that is always correct:

// To really correctly calculate what
// you want, you have to create a proper Dates
// table where each day will have a unique week 
// assigned to it. That's because a week can
// can cross the year's boundary and WEEKNUM
// then does not return what you'd expect.
// So, assume that in a Dates table you've
// got a Date field and a UniqueWeekNumber
// where, again, the latter is an identifier
// of a week which is unique across all years.

[# Cases Closed Same Week] = 
SUMX(
    FILTER(
        Cases,
        NOT ISBLANK( Cases[Closed Date] )
    ),
    var vCreatedDateWeek =
        MAXX(
            FILTER(
                ALL( Dates ),
                Dates[Date] = Cases[Created Date]
            ),
            Dates[UniqueWeekNum]
        )
    var vClosedDateWeek = 
        MAXX(
            FILTER(
                ALL( Dates ),
                Dates[Date] = Cases[Closed Date]
            ),
            Dates[UniqueWeekNum]
        )
    RETURN 
        INT( vCreatedDateWeek = vClosedDateWeek )
)

[Fowmy]

@lilych 

Create the following measure to calculate the count of cases

No of Cases Closed Same Week = 

SUMX(
    Table5,
    IF(
        WEEKNUM(Table5[Closed Date]) = WEEKNUM(Table5[Created Date]),
        1,
        0
    )
)