DAX for Financial Year Running Report Employee

[spandy34]

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

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached files.

[pcoley]

@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
        )
    )

[pcoley]

@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)
    )

[danextian · Accepted solution]

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.

[spandy34]

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

                )

        )

    )