Forum Discussion
Responsive ResidentYearly Running Report Employees within Organisation
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.
Community SupportHi 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.
Responsive Residentdanextian@ 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 -
| 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 |
- v-karpurapud
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.
- spandy34
Hello thank you for the time to respond. Unfortunately I couldnt open the pbix file but I received the following DAX to assist which has worked.
Count 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
)
)
)
- v-karpurapud
Hi spandy34
Glad to hear the issue has been resolved. If you have any other questions, feel free to contact us. We're here to help.Regards,
Microsoft Fabric Community Support Team.