Forum Discussion
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 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
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.
5 Replies
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.I tried and it works. Thankyou
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.
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:- Navigate to Modelling > New Parameter > Fields.
- 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 , 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 ])))
