Determining Status with Start and End Date

[Anonymous]

Hello,

I am currently working with a data set that has a StartDate column and an EndDate column of clients both set of dates tie into a status of the client (ie when they became a client and when they ceased being a client). I'm trying to visualize the month-to-month change in client retention to show the number added per month and the number leaving per month. 

What would be the right approach to model that a status is tied to the start and end date, but also show month to month change of the status?

My thought is that I need some sort of continous date table that is independant of the start and end dates. 

Presently I have 
Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous ,

My thought is that I need some sort of continous date table that is independant of the start and end dates. 

Yes, Please try:

Calendar = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate])) 

Count of active clients = CALCULATE(COUNTROWS('Table'),FILTER('Table',[StartDate]<=MAX('Calendar'[Date]) && [EndDate]>=MAX('Calendar'[Date])))

Output:

 Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790]

Hello @Anonymous ,

Yes, you require a continuous date table to perform all time intellegence functions.

You can create a calendar table either using Power Query or dax. 

And then you can refer this article to write measues to get the required data which you can visualise in whatever way you want.