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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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



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

1newSaturday, July 16, 2022
1activeSaturday, October 1, 2022
1on holdTuesday, October 11, 2022
1activeMonday, October 17, 2022
1completeWednesday, November 2, 2022
2newSaturday, October 1, 2022 
3newSunday, October 16, 2022
3activeMonday, November 14, 2022
4newWednesday, July 20, 2022
4activeSaturday, October 1, 2022
4on holdWednesday, October 12, 2022
5newTuesday, July 19, 2022
5activeSunday, October 2, 2022
6newTuesday, July 19, 2022
6activeSaturday, October 1, 2022
7newWednesday, July 20, 2022


Projects Table 

 Id      Title

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



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

Solution Sage
Solution Sage



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.)

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:

1new 10/3/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! 

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.