topic Re: DAX for Financial Year Running Report Employee in DAX Commands and Tips

DAX for Financial Year Running Report Employee

spandy34 — Fri, 08 May 2026 11:18:41 GMT

I have a Main Employee table that consists Personid, EmpStartDate and EmpEndDate. I need to create a graph that counts the distinct personid for employees in that financial year that were employeed - I wasnt sure whether to post in Deskop or Dax commands so I have posted in both - hope that is okay.

danextian@ v-karpurapud Ashish_Mathur cengizhanarslan

PersonId	EmpStartDate	EmpEndDate
01135190	11/03/1986 00:00
01135191	11/04/1988 00:00	30/09/2018 00:00
01135192	10/09/1979 00:00	31/10/2018 00:00
01135193	15/02/1988 00:00	30/09/2018 00:00
01135194	01/04/2023 00:00	01/04/2023 00:00
01135195	21/09/1987 00:00
01135196	19/09/1988 00:00
01135197	26/03/1977 00:00	31/05/2023 00:00
01135198	29/04/1986 00:00
01135199	28/09/1987 00:00	30/06/2020 00:00
01135200	05/12/1988 00:00
01135201	24/04/1989 00:00
01135202	18/01/1988 00:00
01135203	01/02/1988 00:00	01/02/2024 00:00
01135204	01/02/1988 00:00	23/10/2023 00:00
01135205	05/02/1988 00:00	30/06/2020 00:00
01135206	01/08/1989 00:00	31/08/2019 00:00
01135207	01/02/1990 00:00	30/04/2019 00:00
01135208	12/02/1990 00:00	30/09/2020 00:00
01135209	28/10/1986 00:00
01135210	29/02/1988 00:00
01135211	27/09/1982 00:00	31/03/2021 00:00
01135212	02/01/1990 00:00	31/10/2022 00:00
01135213	04/12/1989 00:00
01135214	15/01/1990 00:00	31/01/2021 00:00
01135215	19/04/1990 00:00	26/04/2020 00:00
01135216	15/10/1990 00:00
01135217	02/07/1990 00:00
01135218	01/02/1989 00:00	01/02/2024 00:00
01135219	12/04/1989 00:00	01/02/2024 00:00
01135220	01/01/2018 00:00	01/02/2024 00:00
01135221	13/06/1988 00:00	30/06/2023 00:00
01135222	17/04/1990 00:00	15/10/2019 00:00

Re: DAX for Financial Year Running Report Employee

danextian — Fri, 08 May 2026 12:11:19 GMT

Hi spandy34

Assuming that in your employee table each employee only has one record and making sure that the date table is disconnected from the employee table, try this:

Count by Time Period = VAR StartDate = MIN ( Dates[Date] ) VAR EndDate = MAX ( Dates[Date] ) RETURN COUNTROWS ( FILTER ( EmpData, EmpData[EmpStartDate] <= EndDate && ( ISBLANK ( EmpData[EmpEndDate] ) || EmpData[EmpEndDate] >= StartDate ) ) )

Please see the attached pbix.

Re: DAX for Financial Year Running Report Employee

pcoley — Fri, 08 May 2026 15:53:48 GMT

spandy34
Please try:

Headcount EOY = VAR FYEndDate = MAX('FY Calendar'[Date]) RETURN CALCULATE( DISTINCTCOUNT(Employee[PersonId]), Employee[EmpStartDate] <= FYEndDate, OR(ISBLANK(Employee[EmpEndDate]), Employee[EmpEndDate] >= FYEndDate) )

Re: DAX for Financial Year Running Report Employee

pcoley — Fri, 08 May 2026 16:07:24 GMT

spandy34
Another option:

Create a Calendar / FY table so you can easily slice by Financial Year if you don´t have one yet.

FY Calendar = 
VAR StartYear = 2020
VAR EndYear   = 2027
RETURN
    ADDCOLUMNS(
        GENERATESERIES(
            DATE(StartYear, 4, 1), 
            DATE(EndYear,   3, 31), 
            1
        ),
        "FY", 
        "FY " & FORMAT([Date], "YYYY") & "/" & FORMAT([Date]+365, "YY")
    )

Then mark this table as a Date table and create a relationship to your Employee table on a date (you can use EmpStartDate or create a duplicate inactive relationship if needed).

And the measure:

Active Headcount FY = 
VAR CurrentFYStart = MIN('FY Calendar'[Date])
VAR CurrentFYEnd   = MAX('FY Calendar'[Date])

RETURN
    CALCULATE(
        DISTINCTCOUNT(Employee[PersonId]),
        Employee[EmpStartDate] <= CurrentFYEnd,
        OR(
            ISBLANK(Employee[EmpEndDate]),
            Employee[EmpEndDate] >= CurrentFYStart
        )
    )

Re: DAX for Financial Year Running Report Employee

Ashish_Mathur — Fri, 08 May 2026 23:15:37 GMT

Hi,

I have solved a similar question in the attached files.

Re: DAX for Financial Year Running Report Employee

spandy34 — Sat, 09 May 2026 00:26:30 GMT

Thank you very much for your response - it has worked and I amended it as the following to refect table name - thanks again

zCount by Time Period =

VAR StartDate =

MIN ( Dates_Main[Date] )

VAR EndDate =

MAX ( Dates_Main[Date] )

RETURN

COUNTROWS (

FILTER (

Combined_Person_Post,

Combined_Person_Post[EmpStartDate] <= EndDate

&& (

ISBLANK ( Combined_Person_Post[EmpEndDate] ) || Combined_Person_Post[EmpEndDate] >= StartDate

)