How do I get the state of a project from the state history table at a given date?

[wmoskal99]

Hello,

Background: I'm working on a report with a table containing project details and a table that contains the state history for all projects.

Issue: I am trying to get the count of statuses for projects at a given date. For example If I set the date to be October 1st, 2022, I should get a count of 3 for "new" and a count of 3 for "active" (Project 1, 4, and 6 would be active on this date, and project 2, 5, and 7 would be new, project 3 would not exist at this point in time).  

Sample Data:

State History Table
 id      State          Date updated

1	new	Saturday, July 16, 2022
1	active	Saturday, October 1, 2022
1	on hold	Tuesday, October 11, 2022
1	active	Monday, October 17, 2022
1	complete	Wednesday, November 2, 2022
2	new	Saturday, October 1, 2022
3	new	Sunday, October 16, 2022
3	active	Monday, November 14, 2022
4	new	Wednesday, July 20, 2022
4	active	Saturday, October 1, 2022
4	on hold	Wednesday, October 12, 2022
5	new	Tuesday, July 19, 2022
5	active	Sunday, October 2, 2022
6	new	Tuesday, July 19, 2022
6	active	Saturday, October 1, 2022
7	new	Wednesday, July 20, 2022

Projects Table 

 Id      Title

1	Design Database
2	Design API
3	Build Frontend
4	Build Database
5	Build API
6	Design Frontend
7	Integrate

Relationship

How would I go about doing this?
Thanks for the help!

[daXtreme]

@wmoskal99 

Here's how I'd do it.

Create a new table (call it, say, Stats) that will store for each project (using Power Query it's simple):

ProjID | Status | Day

The Day column will hold every single day in which the project has the Status. Yes, you want to expand your rows in the table above into a table that has the day granularity. Connect your Projects, Statuses and Calendar dimensions (you've got them, right?) to the table on the relevant fields.

Once this is done, it's all easy-peasy (and fast!) to do all kinds of calculations. Want to see the number of projects that on a particular day have a certain status? Well, drop the status and day onto a table/matrix, create this measure:

[# Projects] = distinctcount( Stats[ProjectID] )

and drop it onto the visual as well.

Now you're done.

(By the way, this is how you create professional models which need to calculate fast.)

[wmoskal99]

Thanks for the response @daXtreme! 

Just so I'm clear, 
If say, project 1 has a "new" status from October 1st to 7th, the table would look like:

id	status	day
1	new	10/1/2022
1	new	10/2/2022
1	new	10/3/2022
1	new	10/4/2022
1	new	10/5/2022
1	new	10/6/2022
1	new	10/7/2022
1	active	10/8/2022
...

? 

Would it be possible to provided the m code for creating such a table in power query?

I also have some similar tables that are wider (such as date history), which contains target start, actual start, target end, and actual end dates for each project, as well as the date the project was updated on. Would this approach still be optimal for that? Is there and column breadth that would be too large for this approach to work?

Thanks again!