Help in Dax .

[uc]

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 or after 31.03.2025 and must registered on or before 31.03.2025). I am looking for the cohort where there is no deduction between the last registration and 01/04/2025.

Registration Date	Deducted Date	Person ID
01.02.2024	25.02.2025	1
01.01.2023	15.06.2024	2
01.01.2015	24.06.2019	3
01.02.2019	22.02.2023	4
27.03.2021	16.09.2023	5
01.03.2025	15.03.2025	1
17.08.2024	31.12.2024	2
03.03.2023	12.12.2024	3
26.04.2024	12.12.2024	4
01.01.2024	08.08.2024	5
24.03.2025		1
01.01.2025		2
01.02.2024	25.03.2025	3
01.01.2025	06.06.2025	4
02.04.2025	30.09.2025	5

 In the above table person id 1,2 and 4 are active as of 31.03.2025. Can you help me to write dax for the above query please? Thanks

[Zanqueta · Accepted solution]

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]

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]

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

[uc]

I tried and it works. Thankyou

[amitchandak]

@uc , seem very similar to HR data, refer if the attached two PBIX can help 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Table'[Date]) && (ISBLANK(Employee[End Date])
|| Employee[End Date]>max('Table'[Date]))),(Employee[Employee Id ])))