Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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:
Proud to be a Super User!
Paul on Linkedin.
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:
Proud to be a Super User!
Paul on Linkedin.
This is great, thank you! I like how it can be used with filters or measures
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!
Here's the results of the three tables...