Help with dax

[uc]

Hello, I wanted to know the number of employees who are active between the financial year 2024/25 (April 2024 to MAr 2025).

Sample table is given below

Registration Date	Deduction 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	4
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	23.03.2025	3
01.01.2025	06.06.2025	4
02.04.2025	30.09.2025	5

Result table must be as below where the latest registration is on or before 31/03/2025 and the deduction date can be empty (which implies they are still active) or  must be on or after 01/04/2024. Can you please help with the dax code for this requirement? Thanks in advance.

Registration Date	Deducted date	Person ID
24.03.2025		1
01.01.2025		2
01.02.2024	25.03.2025	3
01.01.2025	06.06.2025	4
01.01.2024	08.08.2024	5

[Praful_Potphode · Accepted solution]

Hi,

you can create a measure which will give output as 0 or 1 based on your data and then add it to your table visual.

IsActive_FY2425 = 
VAR FY_Start = DATE(2024, 4, 1)  // if you want you can take this from slicer using selected value
VAR FY_End   = DATE(2025, 3, 31)  // if you want you can take this from slicer using selected value

-- 1. Get the current row context from the visual
VAR CurrentPerson = SELECTEDVALUE('YourTable'[Person ID])
VAR CurrentReg    = SELECTEDVALUE('YourTable'[Registration Date])
VAR CurrentDed    = SELECTEDVALUE('YourTable'[Deduction Date])

-- 2. Find the LATEST Registration Date (Chronologically)
-- We only care that the registration happened before the FY ended.
VAR MaxValidRegDate = 
    CALCULATE(
        MAX('YourTable'[Registration Date]),
        FILTER(
            ALLEXCEPT('YourTable', 'YourTable'[Person ID]),
            'YourTable'[Registration Date] <= FY_End
        )
    )

RETURN
    IF(
        -- Check A: Is this the Latest Record?
        CurrentReg = MaxValidRegDate 
        && 
        -- Check B: Is the Deduction Date Valid?
        -- (Logic: It is BLANK (Active) OR it is AFTER the FY Start)
        (
            ISBLANK(CurrentDed) || CurrentDed >= FY_Start
        ),
        1, 
        0
    )

Sample PBIX.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[uc]

Thanks 🙂

[uc]

Hello Praful, thankyou the code works! can you please show me how to set up a slicer in your sample file so that the FY_Start and FY_End can be changed to any date. Thankyou in advance 🙏 

[Praful_Potphode]

Hi @uc ,

Please check if this is what is expecetd.

sample PBIx Page 2

Please give kudos once confirmed.

Thanks and Regards,

Praful

[uc]

Thanks a lot. Much appreciated 🙏

[v-pnaroju-msft]

Thankyou, @amitchandak for your response.

Hi uc,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter:

We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[amitchandak]

@uc , With a date tbale having active join Registration date and inactive join with Deducted date 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Registration Date]<=max('Date'[Date]) && (ISBLANK(Employee[Deducted Date]) || Employee[Deducted Date]>max('Date'[Date]))),(Employee[Person Id ])),CROSSFILTER(Employee[Registration  Date],'Date'[Date],None))

Refer to the file attached after the signature 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU