Cumulative Count of Rows with Start and End Dates

[cathoms]

Hello!

I've spent most of the day looking up possible solutions and have reviewed a dozen or so threads on this forum to no avail. I have a Power BI report with a SharePoint List as the data source. Among others fields, the dataset I built from the list has ID, Start Date, Actual End Date, and a Status field that can include a value for "Complete". I've been asked to show the cumulative or running total count of active projects. I have a Date table with dates connected to the Start Date field in my Projects fact table. I feel like I am close but missing some key element. I'll share the measure I developed, table visuals showing my current results, and then what I hope to acheive.

Measures:

ProjectsCount = DISTINCTCOUNT(Projects[ID])

ProjectsCountTime2 = 
CALCULATE (
    COUNTROWS(Projects),
    FILTER (
        Projects,
        (
        (Projects[Start Date] >= MIN ( Projects[Start Date] )
            && Projects[Start Date] <= MAX(Projects[Start Date]))
        && ISBLANK(Projects[Actual End Date])
       )
   )
)

What I get:

What I trying to get:

Ultimately I want to create either a line graph or bar chart showing the cumulative active projects over time (by Year-Month). Something like this:

Any help would be much appreciated!

[Anonymous]

Hi @cathoms,

Are you able to solve this probem ?

If Yes, can you please share the solution for the same. Since I am also facing the same issue.

Thanks In advance.

Aakash

[cathoms]

The solution is posted above. The last measure I posted worked when I marked my date dimension table as a date table.

[amitchandak · Accepted solution]

@cathoms , You have to use a date table in such cases, refer to my HR blog or files attached after signature on the similar topic

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[cathoms]

Alright, so this is really embarassing but it turns out I neglected to mark my Date_LOOKUP table as a date table... 😳

[cathoms]

I tried a version of that yesterday and this morning emulated your "Current Employees" measure but got the same results. This is with a Date table with inactive relationships between dates and Start Date and Actual End Date. My measure meant to copy "Current Employees" looks like this:

Active Projects = 
CALCULATE (
    COUNTX (
        FILTER (
            Projects,
            Projects[Start Date] <= MAX ( Date_LOOKUP[Dates] )
                && (
                    ISBLANK ( Projects[Actual End Date] )
                )
        ),
        ( Projects[ID] )
    )
)