Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
5 months ago
Solved

DAX for Financial Year Running Report Employee

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 ...
  • danextian's avatar
    danextian
    5 months ago

    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.

pcoley's avatar
pcoley
Icon for Super User rankSuper User
5 months ago

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