Forum Discussion

uc's avatar
uc
Helper II
9 months ago
Solved

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...
Need Help
  • Zanqueta's avatar
    Zanqueta
    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.