Forum Discussion
DAX Help in Finding NEW Service
Hi
I am new to DAX and need help with the below issue. I need to find if a service is new or ongoing based on following conditions.
For DP Service - if a customer has received the same service more than once and the difference in (Next Service Start Date - Previous Service End Date) >1, then it's a 'New' service.
For HC Service - if a customer has received the same service more than once and the difference in (Next Service Start Date - Previous Service End Date )>7, then it's a 'New' service.
I need to produce result as shown below.
| Customer ID | Service Type | Start Date | End Date | Rankx | Duplicate Count | RESULT |
| 21563 | DP | 17/06/2019 | 1 | 2 | New | |
| 21563 | DP | 03/04/2020 | 2 | 2 | New | |
| 21563 | HC | 19/01/2017 | 06/01/2019 | 1 | 3 | New |
| 21563 | HC | 07/01/2019 | 01/04/2020 | 2 | 3 | |
| 21563 | HC | 03/04/2020 | 3 | 3 | ||
| 89326 | DP | 21/01/2019 | 18/07/2019 | 1 | 2 | New |
| 89326 | DP | 19/07/2019 | 22/08/2019 | 2 | 2 | |
| 89326 | HC | 18/06/2016 | 22/08/2019 | 1 | 2 | New |
| 89326 | HC | 05/09/2019 | 31/12/2019 | 2 | 2 | New |
Thanks in advance.
2 Replies
This is actually quite complex, but it has been solved before. I have followed this pattern and it works as advertised. https://www.daxpatterns.com/new-and-returning-customers/
Result := VAR _CurrentStartDate = MAX ( Table2[Start Date] ) VAR _PreviousStartDate = CALCULATE ( MAX ( Table2[Start Date] ), Table2[Start Date] < _CurrentStartDate, ALLEXCEPT ( Table2, Table2[Customer ID], Table2[Service Type] ) ) VAR _PreviousEndDate = CALCULATE ( MAX ( Table2[End Date] ), Table2[Start Date] = _PreviousStartDate, ALLEXCEPT ( Table2, Table2[Customer ID], Table2[Service Type] ) ) VAR _CurrentType = MAX ( Table2[Service Type] ) RETURN IF ( _CurrentStartDate - _PreviousEndDate > IF ( _CurrentType = "PD", 1, 7 ), "New" )
