Hi all,
i am trying to calculate number of clients who have gone a certain number of days without a service. Below is the table i am working with.
| Client | Service Date | Exit Date |
| 1 | 1/01/2023 | 1/02/2023 |
| 1 | 6/01/2023 | 1/02/2023 |
| 2 | 1/02/2023 | 1/03/2023 |
| 3 | 1/03/2023 | 1/05/2023 |
| 3 | 6/04/2023 | 1/05/2023 |
| 4 | 1/04/2023 | 1/05/2023 |
| 5 | 1/05/2023 | 1/06/2023 |
| 5 | 1/06/2023 | 1/06/2023 |
| 6 | 1/06/2023 | 1/07/2023 |
| 7 | 1/07/2023 | |
| 7 | 1/08/2023 | |
| 8 | 1/08/2023 | |
My current dax is calculating the datediff between the service date and and todays date, and the exit date and todays date, and if the service period is over 120 days, it counts the client, and if the client had left outside of 120 days since last service, they will be counted.
My issue is i am using the service date on the page to identify active clients during a period of time, which means when the Datediff is calculating the start date, its counting from the last service of the service date and todays date. Meaning if the service date filter page is set to looking at clients with an activity from 1/1/23 - 1/4/23, instead of client one only having 90 days (As set as the service date filter), its counted as having 240 days, so its been counted as overdue, even though during the selected period of time, they would of still been in date.
How can i have the "date2" in datediff linked to the page filtered date, instead of having it as TODAYS()?
Var Filtered = FILTER(Table1, Table1[Client] in {"Filter1"})
Var ClientMaxDate = SUMMARIZE(Filtered,
Table1[Client],
"overdue", DATEDIFF(MAX(Table1[Servicedate]), TODAY(), DAY),
"Exit", IF(ISBLANK(MAX(Table1[ExitDate])), DATEDIFF(MAX(Table1[Servicedate]), TODAY(), DAY), DATEDIFF(MAX(Table1[Servicedate]), Table1[ExitDate]), DAY)))
Var OverdueClients = FILTER(ClientMaxDate, [overdue] >=120 && [Exit] >=120)
Return
COUNTROWS(OverdueClients)
Thank you for your suppport.
