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. 

 

idDiagnosisDateTimeCompositeKey 
856965729/07/20241-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
803908502/04/20241-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
704473907/07/20231-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
667808003/04/20231-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
592956401/09/20221-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
  count 5
idDiagnosisDateTimeCompositeKey 
856965729/07/20241-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
803908502/04/20241-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
704473907/07/20231-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34with in 60 days
704473808/07/20231-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34with in 60 days
592956401/09/20221-3-2020-Hanndyr-EYDEHAVN-2-360-Arendal-34 
    
  Count 4
  • 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

     

5 Replies

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

     

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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