Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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
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
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.
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
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.
Proud to be a 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)
)
User | Count |
---|---|
116 | |
73 | |
58 | |
49 | |
48 |
User | Count |
---|---|
171 | |
122 | |
60 | |
59 | |
56 |