Forum Discussion
Determining Status with Start and End Date
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!
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.
2 Replies
PC2790Community Champion
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.
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.