Forum Discussion
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 | 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 |
- Anonymous2 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
- AmiraBedh
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) ) - ryan_mayu
Super User
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.
- AnonymousNot 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
- Puja_Kumari25
Helper III
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.
- AnonymousNot 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