how to summarize table of employees until terminat...

eliasayyy · ‎12-27-2022

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

Anonymous · ‎12-29-2022

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