Calculate days between a visit based on a name but less than 180 days apart

[Anonymous]

Hello there,

I have a table with names, including a start and end date of a visit. Now am I trying to get an indicator which shows if a name has been visited in the last 180 days based on the last known end date of that visit.

Example:

Name	Start	End	Visited?
Mister A	09-08-2019		Recently
Mister B	11-07-2019	12-07-2019	Not recently
Mister C	06-08-2019	10-08-2019	Not recently
Mister A	01-07-2019	03-07-2019	Not recently
Mister C	03-08-2018	05-08-2018	Recently
Mister C	01-06-2018	03-06-2018	Not recently

So Mister A has an end date of the very first visit on 03-07-2019 and now a second visit start on 09-08-2019 which is less then 180days apart. So he was recently visited.

Mister B is a new entry so is marked as “not recently”.

Mister C however had his first visit ( and thus as new entry gets marked with “not recently” ) ended on 03-06-2018 and his second visit started on 03-08-2018 which gets marked as “recently”. His third visit started on 06-08-2019, greater than 180 days apart from his second visit so this visit gets marked as “not recently”.

I want to be able to report how often a visit occurs by the same name, less then 180 days apart.

I'm fairly new with DAX and have searched around but couldn't find anything relatedand can't figure this out by my self.

Is there any way to get this working via DAX ? 

I hope you guys understand what I mean as English is not my main tongue.

Please help ?

[OwenAuger · Accepted solution]

Hello @Anonymous 

A calculated column like this should work:

Visited? = 
VAR CurrentRowStart =
    'YourTable'[Start]
VAR MostRecentEnd = 
    CALCULATE (
        MAX ( 'YourTable'[End] ), // Most recent End date
        ALLEXCEPT ( 'YourTable', 'YourTable'[Name] ), // Retain only the Name filter
        AND ( 'YourTable'[End] < CurrentRowStart, NOT ISBLANK ( 'YourTable'[End] ) ) // Only for End Date < current Start Date
    )
RETURN
    IF (
        CurrentRowStart - MostRecentEnd <= 180,
        "Recently",
        "Not recently"
    )

The logic is to find the most recent (i.e. maximum) End Date for rows with Name matching the current row and End Date < current row's Start Date. Then check if that date is within 180 days of the current row's Start Date.

Not sure on your locale but you may need to change commas to semicolons.

Best regards,

Owen

[Anonymous]

---

@OwenAuger wrote:

Hello @Anonymous 

A calculated column like this should work:

Visited? = 
VAR CurrentRowStart =
    'YourTable'[Start]
VAR MostRecentEnd = 
    CALCULATE (
        MAX ( 'YourTable'[End] ), // Most recent End date
        ALLEXCEPT ( 'YourTable', 'YourTable'[Name] ), // Retain only the Name filter
        AND ( 'YourTable'[End] < CurrentRowStart, NOT ISBLANK ( 'YourTable'[End] ) ) // Only for End Date < current Start Date
    )
RETURN
    IF (
        CurrentRowStart - MostRecentEnd <= 180,
        "Recently",
        "Not recently"
    )

The logic is to find the most recent (i.e. maximum) End Date for rows with Name matching the current row and End Date < current row's Start Date. Then check if that date is within 180 days of the current row's Start Date.

Not sure on your locale but you may need to change commas to semicolons.

Best regards,

Owen

---

Wow Owen, many thanks! This works exactly the way i wanted! Much appreciated, i don't think i would have figured this one out myself any time soon 🙂