topic Re: how to summarize table of employees until terminated in DAX Commands and Tips

how to summarize table of employees until terminated

eliasayyy — Wed, 28 Dec 2022 01:08:35 GMT

hello everyone a little complex question,

i have a table and heres a sample:

Id	Name	Position	Update Date	Transport Per Day	Pay Per Hour	Start Date	Terminated
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022
1	Person A	Team Leader	12/1/2022	20	9
2	Person B	Agent	10/1/2022	20	5	10/1/2022
2	Person B	Agent	11/20/2022	0	0		Yes
3	Person C	Agent	11/20/2022	20	5	11/1/2022

so i made on power query following formulas:

Add column name: Today formula = if [Terminated] = "Yes" then [Updated Date] else DateTime.LocalNow() then Add column name: new Date formula = { Number.From([Update Date])..Number.From([Today]) } press expand to new rows change type of new Date to Date

this will create a continuous date from the date until today
so i will have now as result example:

Id	Name	Position	Update Date	Transport Per Day	Pay Per Hour	Start Date	Terminated	Today	new Date
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022		12/28/2022	10/1/2022
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022		12/28/2022	10/2/2022
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022		12/28/2022	10/3/2022
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022		12/28/2022	10/4/2022
…	…	…	…	…	…	…	…	…
1	Person A	Team Leader	10/1/2022	20	9			12/28/2022	12/28/2022
1	Person A	Team Leader	12/1/2022	20	9			12/28/2022	12/1/2022
1	Person A	Team Leader	12/1/2022	20	9			12/28/2022	12/2/2022
1	Person A	Team Leader	12/1/2022	20	9			12/28/2022	12/3/2022
1	Person A	Team Leader	12/1/2022	20	9			12/28/2022	12/4/2022
…	…	…	…	…	…	…	…	…	…
1	Person A	Team Leader	12/1/2022	20	9			12/28/2022	12/28/2022

now if you made the sample yourself,
you will see that Person B will have same effect of Person A as show above but i will have terminated Too
so the list will keep going on and will avoid terminated

what can i do to create list of the employee from the updated date until the last updated date where terminated = "Yes"

so in sample i will have for peron B for the "Today" added column:

Id	Name	Position	Update Date	Transport Per Day	Pay Per Hour	Start Date	Terminated	Today
1	Person A	Team Leader	10/1/2022	20	7	10/1/2022		12/28/2022
1	Person A	Team Leader	12/1/2022	20	9			12/29/2022
2	Person B	Agent	10/1/2022	20	5	10/1/2022		11/20/2022
2	Person B	Agent	11/20/2022	0	0		Yes	11/20/2022
3	Person C	Agent	11/20/2022	20	5	11/1/2022		12/28/2022

Re: how to summarize table of employees until terminated

amitchandak — Wed, 28 Dec 2022 02:54:14 GMT

eliasayyy , refer if this can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Re: how to summarize table of employees until terminated

Anonymous — Thu, 29 Dec 2022 08:19:42 GMT

Hi eliasayyy ,

What's your expected result? Do you want to get the employees who are terminated or something else? Could you please explain more details(specific examples, calculation logic or screenshot etc.) on it? It is better if you can share a simplified pbix file with the expected result explanation. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards