topic Re: Help with dax in DAX Commands and Tips

Help with dax

uc — Tue, 25 Nov 2025 12:56:12 GMT

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

Re: Help with dax

amitchandak — Tue, 25 Nov 2025 13:04:28 GMT

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-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Re: Help with dax

v-pnaroju-msft — Wed, 26 Nov 2025 16:57:47 GMT

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.

Re: Help with dax

Praful_Potphode — Thu, 27 Nov 2025 04:29:56 GMT

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

Re: Help with dax

uc — Thu, 27 Nov 2025 10:56:41 GMT

Thanks a lot. Much appreciated 🙏

Re: Help with dax

uc — Fri, 28 Nov 2025 06:43:40 GMT

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 🙏

Re: Help with dax

Praful_Potphode — Fri, 28 Nov 2025 07:58:16 GMT

Hi uc ,

Please check if this is what is expecetd.

sample PBIx Page 2

Please give kudos once confirmed.

Thanks and Regards,

Praful

Re: Help with dax

uc — Tue, 02 Dec 2025 14:28:08 GMT

Thanks 🙂