Counting number of customer calls that resulted in a meeting the same or next day

[Proctopus]

Hello,

If I have a table of records showing each customer contact (phone/video call, in-person meeting), how can I count the number of one type of contact that resulted in another on the same or next day - specifically, how many video calls resulted in an in-person meeting the same or next day?

I've come up with the solution outlined below, but one of the issues I'm encountering with this approach is when I put the measure into a visual and slice by my date table. In instances where the video call occurs at the end of the month, but the meeting occurs at the beginning of the next month (e.g., call on 31 January, meeting on 1 February), the visual does not know which month to put this measure result in. Ideally, the visual would "anchor" with the date of the video call (i.e., the first event - continuing with example above, this would be 31 January). It does work if I use the date from the fact table, but this is not ideal since I can't group by month, etc. How can I address this issue?

Any questions or if more info is needed, please let me know. Thank you for your time and assistance!

My sample data (date table is continuous dates for the full years spanning the data, in this case, just 2020):

Dept	Cust Id	Type	Date
A	123	In-person	2020-01-01
A	123	Video	2020-01-03
B	123	In-person	2020-01-05
B	123	Phone	2020-01-07
C	234	Phone	2020-01-15
C	234	Video	2020-01-28
A	234	Video	2020-01-29
A	234	In-person	2020-01-30
A	345	In-person	2020-02-01
A	345	Phone	2020-02-02
A	345	Video	2020-02-03
A	345	Phone	2020-02-03
A	345	In-person	2020-02-03
B	234	Video	2020-01-31
B	234	In-person	2020-02-01

The sample model:

The DAX measure I tried:

Count video encounters with in-person visits 0-1 day later = 
var VidInpDates =
    ADDCOLUMNS (
        SUMMARIZE ( 'Customer Encounters',
            'Customer Encounters'[Cust Id],
            'Customer Encounters'[Type],
            'Customer Encounters'[Date]
        ),
        "ThisVidDate", CALCULATE ( MAX ( 'Customer Encounters'[Date] ), 'Customer Encounters'[Type] = "Video" ),
        "NextInpDate", CALCULATE (
            CALCULATE (
                MIN ( 'Customer Encounters'[Date] ),
                'Customer Encounters'[Type] = "In-person",
                FILTER ( ALL ( 'Customer Encounters'[Date] ),
                    'Customer Encounters'[Date] >= MAX ( 'Customer Encounters'[Date] )
                )
            )
        )
    )
var VidInpDiff =
    ADDCOLUMNS (
        VidInpDates,
        "DaysBtwn", DATEDIFF ( [ThisVidDate], [NextInpDate], DAY )
    )
var ZeroToOneCount =
    COUNTROWS (
        FILTER ( VidInpDiff,
            'Customer Encounters'[Type] = "Video"
                && [DaysBtwn] >= 0
                && [DaysBtwn] <= 1
                && NOT ( ISBLANK ( [DaysBtwn] ) )
        )
    )
return ZeroToOneCount

Sample matrix visuals showing missing measure result when video call occurs in one month, and in-person occurs in another:

[mahoneypat · Accepted solution]

Please try this expression, as it gets your expected results from your sample results.

Video In Person 1 Day Count = 
VAR summary =
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                'Customer Encounters',
                'Customer Encounters'[Cust Id],
                'Customer Encounters'[Date],
                'Customer Encounters'[Type]
            ),
            'Customer Encounters'[Type] = "Video"
        ),
        "@InPerson1Day", CALCULATE (
            COUNTROWS ( 'Customer Encounters' ),
            ALL ( 'Date' ),
            ALL ( 'Customer Encounters'[Date], 'Customer Encounters'[Type] ),
            'Customer Encounters'[Type] = "In-person",
            FILTER (
                ALL ( 'Customer Encounters'[Date] ),
                OR (
                    'Customer Encounters'[Date] = EARLIER ( 'Customer Encounters'[Date] ),
                    'Customer Encounters'[Date]
                        = EARLIER ( 'Customer Encounters'[Date] ) + 1
                )
            )
        )
    )
RETURN
    COUNTROWS(Filter(summary, [@InPerson1Day]>0))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Proctopus]

Thank you, @mahoneypat, that solution worked! I'm not very good at using the EARLIER function so this solution provides another example for me to better understand it. Cheers!