Sum of count of filtered values by 60 days within a date

[kerya]

Case ID	Location	Date
1	123	9/22/21
2	456	9/1/21
3	678	6/24/21
4	123	7/15/21
5	123	9/5/21

Location	End Date
123	8/25/21
123	9/30/21
678	6/30/21

I am trying to figure out how I would count the case ID's in the first table within 60 days of each respective location's contract end date. Some locations have multiple contract end date's, so I would also be trying to sum the results from both of those counts. I haven't had any luck setting up this formula yet.

[Anonymous · Accepted solution]

Hi @kerya ,

You can create a measure like

Count =
SUMX (
    'Table (2)',
    CALCULATE (
        COUNT ( 'Table'[Case ID] ),
        FILTER (
            'Table',
            [Date] <= MAX ( 'Table (2)'[End Date] )
                && [Date]
                    >= MAX ( 'Table (2)'[End Date] ) - 60
        )
    )
)

Each row is the count corresponding to Location and End Date, and Total is the sum of all.

Best Regards,

Stephen Tao

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

[Ashish_Mathur]

Hi,

Show the expected result please.