Calculate count based on date Range : count records if the current record date and previous records

[Puja_Kumari25]

Hi All,

Please help me with Dax, I need to count records if the current record date and previous records date within 60 days then one, and if greater than 60 count separately. 

id	DiagnosisDateTime	CompositeKey
8569657	29/07/2024	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
8039085	02/04/2024	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
7044739	07/07/2023	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
6678080	03/04/2023	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
5929564	01/09/2022	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
		count	5
id	DiagnosisDateTime	CompositeKey
8569657	29/07/2024	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
8039085	02/04/2024	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
7044739	07/07/2023	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34	with in 60 days
7044738	08/07/2023	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34	with in 60 days
5929564	01/09/2022	1-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34
		Count	4

[v-yangliu-msft · Accepted solution]

Hi  @Puja_Kumari25 ,

Does the group you describe refer to [CompositeKey], which is calculated according to the [CompositeKey] grouping, you can modify to the following dax:

Create calculated column.

Test1 =
var _last=
MAXX(
    FILTER(ALL('Table'),'Table'[DiagnosisDateTime]<EARLIER('Table'[DiagnosisDateTime])&&'Table'[CompositeKey]=EARLIER('Table'[CompositeKey])),[DiagnosisDateTime])
var _if=
DATEDIFF(
    _last,'Table'[DiagnosisDateTime],DAY)
return
IF(
    _if=BLANK(),61,_if
)

Test2 =
COUNTX(
    FILTER(ALL('Table'),
    'Table'[CompositeKey]=EARLIER('Table'[CompositeKey])&&
    [Test1]>60),[id])

Best Regards,

Liu Yang

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

[Puja_Kumari25]

Thanks for the reply, we need to count 60 days in a group only. Like here there will be multiple composite keys in the table.  For the same composite key group, 60 days logic will be implemented, otherwise for different not.

[v-yangliu-msft]

Thanks for the reply from @AmiraBedh  and @ryan_mayu , please allow me to provide another insight: 
Hi  @Puja_Kumari25 ,

Here are the steps you can follow：

1. Create calculated column.

Test1 =
var _last=
MAXX(
    FILTER(ALL('Table'),'Table'[DiagnosisDateTime]<EARLIER('Table'[DiagnosisDateTime])),[DiagnosisDateTime])
return
DATEDIFF(
    _last,'Table'[DiagnosisDateTime],DAY)

Test2 =
COUNTX(
    FILTER(ALL('Table'),
    [Test1]>60||[Test1]=BLANK()),[id])

2. Result:

Best Regards,

Liu Yang

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

[ryan_mayu]

@Puja_Kumari25 

so you have two tables? You need to compare the date between these two tables? why the 4th row is within 60 days?

i saw the dates are  3rd Apr and 8th Jul.

[AmiraBedh]

Sort your table with the DiagnosisDateTime column.

Next, create this CC to ount the records within the 60 days window for each record

Within60Days =
VAR CurrentDate = 'Table'[DiagnosisDateTime]
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[DiagnosisDateTime] <= CurrentDate &&
'Table'[DiagnosisDateTime] > CurrentDate - 60
)
)

Then, create a measure to count the records based on the Within60Days :

CountWithin60Days =
SUMX(
'Table',
IF('Table'[Within60Days] > 0, 1, 0)
)