Forum Discussion

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

Yearly Running Report Employees within Organisation

I have a table that has every employee in the organisation since 1996 and I have two tables person and post that are linked by personid

the person table has fields such as name , personid, empno, empstartdate, empenddate. gender. The post table has details relating to the posts that the employee has held and includes fields such as personid, mainpost (Y/N), poststartdate, postenddate, post title, grade, postnumber, FTE, Contracttype.

I want to create a graph as below which calculates the number Headcount (count of personid) and also the SUM of the FTE field for each year.

This is a live datasource which changes daily when people are recruited and leave the organisation.

Can ayone help with the Meausre or a way of how this can be achieved. By the way, there is a date table in the report.

 

amitchandak tamerj1 danextian goncalogeraldes 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 months ago

    Hi,

    Please share some data to work with and show the expected result in a simple Table format.  If possible, please share the download link of an MS Excel file with your formula shown there.  I will understand those Excel formulas and convert them into DAX measures.

11 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    7 months ago

    1) Put Year from the Date table on the axis 

     

    2) Headcount

    Headcount =
VAR AsOfDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( Person[PersonID] ),
    FILTER (
        ALL ( Person ),
        Person[EmpStartDate] <= AsOfDate
            && ( ISBLANK ( Person[EmpEndDate] ) || Person[EmpEndDate] >= AsOfDate )
    )
)

     

    3) FTE

    FTE =
VAR AsOfDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    SUM ( Post[FTE] ),
    FILTER (
        ALL ( Post ),
        Post[PostStartDate] <= AsOfDate && ( ISBLANK ( Post[PostEndDate] ) || Post[PostEndDate] >= AsOfDate )
    )
)

     

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

    Do I link the date table to the EmpStartDate or the PostStartDate?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 months ago

    Hi,

    Please share some data to work with and show the expected result in a simple Table format.  If possible, please share the download link of an MS Excel file with your formula shown there.  I will understand those Excel formulas and convert them into DAX measures.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    7 months ago

    As always, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi spandy34 

    We have not received an update from you for some time. To assist you in resolving the issue, we kindly request that you provide the necessary details. Once we have this information, we will be able to address your concern effectively.

    Thank you.

     

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

      danextian@ v-karpurapud  Ashish_Mathur cengizhanarslan 

       

       

      Sorry for the delay - I have been trying to combine the data - so here is the table and the needed table below based on the number of Personid that was employed each financial year - 

       

       

       

      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
      • v-karpurapud's avatar
        v-karpurapud
        Icon for Community Support rankCommunity Support
        4 months ago

        Hi spandy34 


        Thank you for providing the data. We have implemented the required DAX calculations and created the report successfully. I have attached the .pbix file along with a snapshot of the output for your reference. Please take a moment to review it.


        The report currently shows the yearly headcount trend based on employees who were active during each financial year.

        I hope this matches your requirement. If I have misunderstood any part of your scenario, please share additional details and I will be happy to adjust the solution accordingly.

        Regards,
        Microsoft Fabric Community Support Team.