Total count based on the recurrence of the same event within a speficic time period.

[Puja_Kumari25]

Hi  All,

Please help me to find the total count based on the recurrence of the same event within a period.

ID diseases diseases date
1 D1 22-06-2024
2 D2 25-06-2024
3 D2 26-06-2024
3 D3 22-07-2024
1 D4 22-07-2024
2 D4 27-07-2024
3 D2 27-07-2024
1 D1 22-07-2024
1 D2 22-05-2024

Count ID Count
7

1 {D1, D1} ,D2, D4 -(3) As D1 recurrence within 60 days.

2 D2 , D4 - 2

3 {D2,D2,}, D3 -2 As D2 recurrence within 60 days.

Thanks

[Puja_Kumari25]

Thank you for sharing the solution.

[Rupak_bi]

HI @Puja_Kumari25 
Please elaborate your desired output. Not able to understand the the current format.

[Puja_Kumari25]

We need to create a measure to  total count  but condition is that for the same id , same event (like D1,D2 ) occur within a specific date its count only one.  here is total count of rows is 9, but output form mesure should be 7. 

[v-zhengdxu-msft · Accepted solution]

Hi @Puja_Kumari25 

Please try this:

Here is the sample table:

Then add a measure:

MEASURE =
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Datediff",
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[diseases date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[diseases date] < EARLIER ( 'Table'[diseases date] )
                            && 'Table'[diseases] = EARLIER ( 'Table'[diseases] )
                            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
                    )
                ),
                'Table'[diseases date],
                DAY
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( _vtable, [_Datediff] >= 60 ),
            'Table'[ID],
            'Table'[diseases]
        )
    )
        + COUNTROWS (
            SUMMARIZE (
                FILTER ( _vtable, [_Datediff] < 60 ),
                'Table'[ID],
                'Table'[diseases]
            )
        )

The result is as follow:

Best Regards

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

[Puja_Kumari25]

Sample data be like 

For example  :

ID diseases diseases date
1 D1 22-06-2024
2 D2 25-06-2024
3 D2 26-06-2024
3 D3 22-07-2024
1 D4 22-07-2024
2 D4 27-07-2024
3 D2 27-07-2024
1 D1 22-07-2024
1 D2 22-05-2024