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.

     

     



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

 

 



uc's avatar
uc
Helper II
9 months ago

Hello Zanqueta

Your code works thankyou. However in future if we want to change the reference date from 31.03.2025  to some other date say 31.03.2024, is there a way we can pass the value from the front end (report view) instead of going to the table view and change the date there? Thankyou in advance.

 

  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago

    Hi uc,

     

    Yes, off course. The better way for me is creating a parameter

    To allow users to dynamically select a reference date from the report interface, rather than hardcoding it within a DAX measure. This can be achieved by creating a parameter in Power BI Desktop that is exposed via a slicer.

     

    Steps to Create a Date Parameter in Power BI

    1. Create the Parameter

    In Power BI Desktop:
    1. Navigate to Modelling > New Parameter > Fields.
    2. Configure the parameter as follows:
      • Name: ReferenceDate
      • Data Type: Date
      • Selection Mode: List of values or Range
      • Values: Enter the dates you wish to make available (e.g., 31/03/2024, 31/03/2025)
      • Default Value: Select an initial default date

     

    Use Parameters to Visualize Variables - Power BI | Microsoft Learn