Help Filtering Table

[garrenr14]

Hello all,

Given the following data set:

Name	Date Seen	Relative Date Seen
Liam	1/8/2021	Today
Noah	1/8/2021	Today
Emma	1/8/2021	Today
Oliver	1/8/2021	Today
Ava	1/8/2021	Today
William	1/8/2021	Today
Olivia	1/7/2021	Yesterday
Emma	1/7/2021	Yesterday
William	1/7/2021	Yesterday
Sophia	1/7/2021	Yesterday
Olivia	1/6/2021	Two Days Ago
Noah	1/6/2021	Two Days Ago
Emma	1/6/2021	Two Days Ago
Ava	1/6/2021	Two Days Ago
William	1/6/2021	Two Days Ago
Sophia	1/6/2021	Two Days Ago

Would it be possible to generate the following three lists?

New People - Not Seen Before Today

Liam

Oliver

Missing People - Seen Yesterday but not Today

Olivia

Sophia

Returning People - Seen Two days ago and Today but not Yesterday

Noah

Ava

It feels like this should be a basic exercise in using filters but I can't seem to figure it out.

Ideally, the 'relative date' would be a slicer / filter of some sort, so that this view could be generated for previous days.

Also, I'm open to hearing your thoughts on the best way to visualize this data. At this time I can only think of a list, but there might be other neat visuals like timelines that would make this data pop.

Thanks in advance for all your help!

[PaulDBrown · Accepted solution]

@garrenr14 

You can create measures as follows:

1) New People:

New people = 
VAR people = VALUES(Table1[Name])
VAR BeforeToday = CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] <> "Today")
RETURN
COUNTROWS(
      EXCEPT(people, BeforeToday))

2) Seen Yesterday but not today:

Seen Yesterday but not today = 
VAR SToday = CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] = "Today")
VAR SYesterday = CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] = "Yesterday")
Return
COUNTROWS(
      EXCEPT(SYesterday, SToday)
)

3) Returning People:

Returning People = 
VAR _yesterday = CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] = "Yesterday")
VAR _Today= CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] = "Today")
VAR Days2Ago = CALCULATETABLE(VALUES(Table1[Name]), Table1[Relative Date Seen] = "Two Days Ago")
RETURN
COUNTROWS(EXCEPT(
            INTERSECT(_Today, Days2Ago), _yesterday))

You can then use these as either measures in a table:

or as filter in the filter pane for each visual:

[garrenr14]

This is great, thank you! I like how it can be used with filters or measures

[littlemojopuppy]

Hi  

Sure we can do that!  I added a calculated column like below and then it becomes basic set operations...

Days Since Last Visit = DATEDIFF( 'People Visits'[Date Seen], TODAY(), DAY )

People Not Seen Before Today = 
    VAR PeopleSeenToday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 0
            )
        )
    VAR PeopleSeenBeforeToday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] <> 0
            )
        )
    RETURN

    EXCEPT(
        PeopleSeenToday,
        PeopleSeenBeforeToday
    )

People Seen Yesterday But Not Today = 
    VAR PeopleSeenToday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 0
            )
        )
    VAR PeopleSeenYesterday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 1
            )
        )
    RETURN

    EXCEPT(
        PeopleSeenYesterday,
        PeopleSeenToday
    )

People Seen Yesterday and Day Before Not Today = 
    VAR PeopleSeenToday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 0
            )
        )
    VAR PeopleSeenYesterday =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 1
            )
        )
    VAR PeopleSeenTwoDaysAgo =
        CALCULATETABLE(
            VALUES('People Visits'[Name]),
            FILTER(
                ALL('People Visits'),
                'People Visits'[Days Since Last Visit] = 2
            )
        ) 
    RETURN

    EXCEPT(
        INTERSECT(
            PeopleSeenToday,
            PeopleSeenTwoDaysAgo
        ),
        PeopleSeenYesterday
    )

Hope this helps!  

[littlemojopuppy]

Here's the results of the three tables...