topic Total count based on the recurrence of the same event within a speficic time period. in DAX Commands and Tips

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

Puja_Kumari25 — Tue, 03 Sep 2024 16:27:58 GMT

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

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

Puja_Kumari25 — Tue, 03 Sep 2024 07:07:56 GMT

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

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

Rupak_bi — Tue, 03 Sep 2024 12:21:38 GMT

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

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

Puja_Kumari25 — Tue, 03 Sep 2024 16:30:19 GMT

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.

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

Anonymous — Wed, 04 Sep 2024 06:10:27 GMT

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.

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

Puja_Kumari25 — Tue, 10 Sep 2024 10:23:07 GMT

Thank you for sharing the solution.