Customer Number and Turnover from 1 table (customer actions)

[alya1]

Hello, 

I tried to simplifiy the background by using just 1 table below: 

customers actions
customer id	action	subscription tier	date	reason
111	activated	a	1/1/2021	sale
222	activated	a	4/1/2021	sale
333	activated	a	4/1/2021	sale
444	activated	b	4/22/2021	friend
111	deactivated	c	4/28/2021	price
555	activated	a	5/1/2021	friend
...
111	reactivated	b	9/30/2023	sale
...

There are many more rows and some customers reactivate/deactivate up to 5 times. 

1) Is there a way to easily calcuate the customer number and turnover by year/month/day? 

2) I tried to look up some staff turnover guides but their data table utilitzes columns ID, start date, end date. If anyone know how I can turn the above table to below, I would greately appreciate it!

ID	Start Date	End Date
111	1/1/2021	4/28/2021
222	4/1/2021
333	4/1/2021
444	4/22/2021
555	5/1/2021
111 (re)	9/30/2023

[ryan_mayu · Accepted solution]

@alya1 

not sure about the first question, what's the expected output based on the sample data you provide?

for the second one, you can create a column

end date = if('Table'[action ]="activated",minx(FILTER('Table','Table'[customer id]=EARLIER('Table'[customer id])&&'Table'[date ]>EARLIER('Table'[date ])),'Table'[date ]))

then you can create a visual and only select activate and reactivate data.

[alya1]

Thank you for your solution, it works! 

It appears my raw data have many missing values/issues so I will try to go about this another way for customer number and turnover. I am mainly trying to make my cleaned up table look like this: https://blog.enterprisedna.co/staff-turnover-calculation-in-power-bi-using-dax-hr-insights/

[ryan_mayu]

you are welcome. If you want to get the solution for another question. It's better to update the sample data and provide the expected output based on it.