Hi,
I am trying to create a measure that totals the amount of people that were last seen 1 year (leaking), 2 years (lost) and 3 years (gone) ago. I have a customer table that has a date of last visit and I presently have everyone assigned to a present-day category just by using this measure:
Customer Status = SWITCH(TRUE(),'customer table'[archive]=1,"Archived",'customer table'[DSLV]=BLANK(),"Prospect",'customer table'[DSLV]<365,"Active",'customer table'[DSLV]<720,"Leaking",'customer table'[DSLV]<1095,"Lost",'customer table'[DSLV]<3650,"Gone", BLANK())
Max CLV = CALCULATE(Max('Appointments'[Appt Date]), 'Appointments'[Status] = "Completed")
This works well except for when I have a date filter on a report page. I would like to make individual date relative measures where there is a measure column for Leaking, Lost and Gone Statuses tied to the last date in the present filter (say for instance that February 2020 would determine the status dates for these people as of 2/29/20. Am I overcomplicating this? I do have an inactive relationship between my date table but can't make it primary.
Customer Table:
| Customer_ID | Max CLV (completed last visit) | DSLV (days since last visit) | Customer Status |
| 1 | 8/3/17 | 1079 | Lost |
| 2 | 1/9/15 | 2016 | Gone |
| 3 | 1/13/20 | 186 | Active |
Appointments Table:
| Customer_ID | Date | Status |
| 1 | 1/1/15 | Completed |
| 1 | 8/3/17 | Completed |
| 2 | 12/18/14 | Completed |
| 2 | 1/9/15 | Completed |
| 3 | 11/13/19 | Completed |
| 3 | 1/13/20 | Completed |
| 3 | 9/2/20 | Not-Confirmed |
Desired Output:
Three measures that count the number of active, leaking, lost and gone people dependent on the Max CLV (date of last completed visit up until the end of the filter) and the date range/last date of the currently selected filter range in the report.
Example:
Filter Range: Dec 2019
Active Measure: Would find the difference between 12/31/2019 and the max completed appt date up until 12/31/19. For customer 3, the MAX DLV for the Dec 2019 filter range would be 11/13/19. So for the Dec 2019 filter range, this person would exist in active because 12/31/19-11/13/19 is less than 1 year and it would assign them to an active measure.
Leaking, Lost and Gone Measures: same logic as above except it would like for spreads of 1 year to 2 years, 2 years to 3 years and then over three years.
If anyone could help, I would certainly appreciate it!
