Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RogerSteinberg
Post Patron
Post Patron

Getting total value per day based on most recent status

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 

DateAwakeAsleepSleepWalking
1/1/2020100
1/2/2020100
1/3/2020010
1/4/2020010
1/5/2020010
1/6/2020010
1/7/2020010
1/8/2020010
1/9/2020010
1/10/2020001

 

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

DateAsleepAwakeSleepWalking
1/1/20221  
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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

ModelModel

Table 1Table 1

Table 2Table 2

 

Measure:

MeasureMeasure

 

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

ModelModel

Table 1Table 1

Table 2Table 2

 

Measure:

MeasureMeasure

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors