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

 

 

 

 

 

PersonIdEmpStartDateEmpEndDate
0113519011/03/1986 00:00 
0113519111/04/1988 00:0030/09/2018 00:00
0113519210/09/1979 00:0031/10/2018 00:00
0113519315/02/1988 00:0030/09/2018 00:00
0113519401/04/2023 00:0001/04/2023 00:00
0113519521/09/1987 00:00 
0113519619/09/1988 00:00 
0113519726/03/1977 00:0031/05/2023 00:00
0113519829/04/1986 00:00 
0113519928/09/1987 00:0030/06/2020 00:00
0113520005/12/1988 00:00 
0113520124/04/1989 00:00 
0113520218/01/1988 00:00 
0113520301/02/1988 00:0001/02/2024 00:00
0113520401/02/1988 00:0023/10/2023 00:00
0113520505/02/1988 00:0030/06/2020 00:00
0113520601/08/1989 00:0031/08/2019 00:00
0113520701/02/1990 00:0030/04/2019 00:00
0113520812/02/1990 00:0030/09/2020 00:00
0113520928/10/1986 00:00 
0113521029/02/1988 00:00 
0113521127/09/1982 00:0031/03/2021 00:00
0113521202/01/1990 00:0031/10/2022 00:00
0113521304/12/1989 00:00 
0113521415/01/1990 00:0031/01/2021 00:00
0113521519/04/1990 00:0026/04/2020 00:00
0113521615/10/1990 00:00 
0113521702/07/1990 00:00 
0113521801/02/1989 00:0001/02/2024 00:00
0113521912/04/1989 00:0001/02/2024 00:00
0113522001/01/2018 00:0001/02/2024 00:00
0113522113/06/1988 00:0030/06/2023 00:00
0113522217/04/1990 00:0015/10/2019 00:00
  • 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.

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    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.

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

      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
                      )
              )
          )
  • pcoley's avatar
    pcoley
    Icon for Super User rankSuper User
    5 months ago

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