Getting total value per day based on most recent status

[RogerSteinberg]

I have two tables.

• A date table with a date column
• A table with data showing: StatusDateChange,UserId,NewState

For each date from the date column, I would like to count for each status group the count of users that fall in that status.

Basically what is the most recent status  up to each date of the row context.

For example, if someone was:

1. Awake on Jan 1st 2020
2. Asleep on Jan 3rd 2020
3. Sleepwalking on Jan 10 2020

I want my matrix to show case the following 

Date	Awake	Asleep	SleepWalking
1/1/2020	1	0	0
1/2/2020	1	0	0
1/3/2020	0	1	0
1/4/2020	0	1	0
1/5/2020	0	1	0
1/6/2020	0	1	0
1/7/2020	0	1	0
1/8/2020	0	1	0
1/9/2020	0	1	0
1/10/2020	0	0	1

Obviously what i currently have when i activate the Show no data option from the date column

Date	Asleep	Awake	SleepWalking
1/1/2022	1
1/2/2022
1/3/2022		1
1/4/2022
1/5/2022
1/6/2022
1/7/2022
1/8/2022
1/9/2022
1/1/2022			1

How do i fill in the gaps ? One important thing to note is that I don't want to cross join my table with every date possibility even though it would work since my table would generate over 100M rows by doing this.

[Anonymous]

If the above solves your problem, please mark it as THE solution so that others have an easier time finding answers. Thanks.

[Anonymous · Accepted solution]

I used my own data, so the figures are correct but different from what you pasted above.

Model

Table 1

Table 2

Measure:

Measure

The measure counts the number of visible users that have their latest status in the set of the visible statuses.