Forum Discussion

Puja_Kumari25's avatar
Puja_Kumari25
Icon for Helper III rankHelper III
2 years ago
Solved

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

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 Diagnos...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

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)
)