Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Puja_Kumari25
Helper II
Helper II

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
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

 

vyangliumsft_0-1722937149746.png

 

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

 

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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

 

vyangliumsft_0-1722937149746.png

 

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
Helper II
Helper II

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
Community Support
Community Support

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:

vyangliumsft_0-1722911091154.png

vyangliumsft_1-1722911091158.png

 

 

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AmiraBedh
Super User
Super User

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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.