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