Forum Discussion
uc
9 months agoHelper II
Help in Dax .
Hello , I have got a table which lists registration and deduction date for every Person ID. I wanted to find out the active cohort as of 31.03.2025 (which means the deducted date should be blank o...
- 9 months ago
Hi uc
I tested here, let me know if it worked for you:IsActive_31Mar2025 = VAR ReferenceDate = DATE(2025, 3, 31) VAR LastRegistration = CALCULATE( MAX(fact_registration[Registration Date]), ALLEXCEPT(fact_registration, fact_registration[Person ID]) ) VAR AssociatedDeduction = CALCULATE( MAX(fact_registration[Deducted Date]), fact_registration[Registration Date] = LastRegistration, ALLEXCEPT(fact_registration, fact_registration[Person ID]) ) RETURN IF( LastRegistration <= ReferenceDate && (ISBLANK(AssociatedDeduction) || AssociatedDeduction > ReferenceDate), 1, 0 )
✅If this response resolved your issue, please mark it as correct to assist other members of the community.
Zanqueta
9 months agoSuper User
Hi uc
I tested here, let me know if it worked for you:
IsActive_31Mar2025 =
VAR ReferenceDate = DATE(2025, 3, 31)
VAR LastRegistration =
CALCULATE(
MAX(fact_registration[Registration Date]),
ALLEXCEPT(fact_registration, fact_registration[Person ID])
)
VAR AssociatedDeduction =
CALCULATE(
MAX(fact_registration[Deducted Date]),
fact_registration[Registration Date] = LastRegistration,
ALLEXCEPT(fact_registration, fact_registration[Person ID])
)
RETURN
IF(
LastRegistration <= ReferenceDate &&
(ISBLANK(AssociatedDeduction) || AssociatedDeduction > ReferenceDate),
1,
0
)
✅If this response resolved your issue, please mark it as correct to assist other members of the community.
uc
9 months agoHelper II
I tried and it works. Thankyou