Distinct count with date parameter

[DanielMedina]

Hello everyone! Hope you all are having a nice day.

I'm looking for some help with something that is stressing me a lot in the past few days.

So, what I'm trying to do is to get the amount of lives each person had to watch since their registration date, and then compare it to how many they really watched. It's for a engagement study I'm doing.

The table should look like this:

Name	Registration Date	[A] lives streamed since registration date	[A] lives watched	[B] lives streamed since registration date	[B] lives watched	[C] lives streamed since registration date	[C] lives watched
Peter	23/08/2021	3	2	2	0	3	1
Jordan	07/11/2016	3	1	2	0	3	1
Alisha	25/10/2021	3	0	2	1	1	1
Mike	21/06/2013	3	0	2	1	3	0

This is the table that I'm getting the info from:

Live Name	Date	Live	Name
Live [A] - 04/12/2021	04/12/2021	Live [A]	Peter
Live [A] - 06/12/2021	06/12/2021	Live [A]	Peter
Live [A] - 12/12/2021	12/12/2021	Live [A]	Jordan
Live [B] - 16/03/2022	16/03/2022	Live [B]	Alisha
Live [B] - 24/04/2022	24/04/2022	Live [B]	Mike
Live [C] - 01/10/2021	01/10/2021	Live [C]	Alisha
Live [C] - 04/11/2021	04/11/2021	Live [C]	Peter
Live [C] - 01/09/2021	01/09/2021	Live [C]	Jordan

I'm using a one to many, crossfiltered relation between these tables.

If anyone knows how to get this to work and could spare some time to explain it to me, I'd really appreciate it!

Thanks in advance.

[lbendlin]

Which table has the Registration Date information?  How does your data model look like?

[DanielMedina]

Hello @lbendlin 
Sorry, I forgot about the table with the registration date. It's this one:

Name	Registration Date	Category
Peter	23/08/2021	Diamond
Jordan	07/11/2016	Gold
Alisha	25/10/2021	Diamond
Mike	21/06/2013	Silver

My data model is this:

An one to many relation with "Reg" crossfiltering the "Table".

[lbendlin · Accepted solution]

You will need to add another dimension table to deal with the blanks. Here is a sample implementation.

[DanielMedina]

Thanks a lot @lbendlin !

It really helped me a lot.