Calculate cumulative counts

[jsquaredz]

I am looking to calculate counts based on a condition during a given date.

I would create a date table

I would add a calculated column in that date table.

That calculated column should look at the given date in the row of the column, and iterate over the entire data table to see how many items were open during that date (Open being opened date >= date in row & date in row < closed date)

Anyway I know I'm close, but I have tried a few attempts and the answer eludes me.  I want the output to look like the report below.

DataReport I would Like

[danrmcallister · Accepted solution]

Another option:

You'll need a date table, you can use something like this (though you'll just really need the date for this): https://community.powerbi.com/t5/Desktop/How-do-i-create-a-date-table/td-p/23896 

On your date table add a column with this formula: OpenIssues = Calculate(Countrows(Table1),Filter(Table1, Table1[Date Opened] <= LASTDATE(DateTable[Date]) && Table1[Date Closed] >= FirstDate(DateTable[Date])))

Gives a result as such with your sample data:

[AnnieMcC]

I have been looking for this solution for days!! This is so good. Thank you!!!!!

[jsquaredz]

Here is my adaptation of your formula.  Unfortunately it doesnt seem to work for me.  I get all rows of the new column with the same value.

OpenIssues = 
Calculate(Countrows(Issues),
Filter(Issues, Issues[Date Opened] <= LASTDATE(DateTable[Date]) 
&& Issues[Date Closed] >= FirstDate(DateTable[Date])
)
)

Can you link your example pbix file so I can take a look through it?

[danrmcallister]

Hmmm, did you build your OpenIssues column on the Date table or the Issues table?  In this design I built it on the Date table.

I'm new to these forums, how do you upload a file?  I'd be happy to email it to you.  In lieu of that here are some screenshots with the details:

[jsquaredz]

I built my openissues column in my date table.  Not sure if it matters but my date table is a calculated table.

PS I sent you a PM with my email.

[Sean]

@jsquaredz

To make @danrmcallister's column work you need to get rid of the relationship between the tables!

There's even a third way to do this if you are interested...

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

Which way you go it really depends on how much further analysis you need to do!

Read @KHorseman's posts at the above link!

BTW

Here's the Active Count formula explained

http://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-cam...

[jsquaredz]

That was it.  Needed to turn off those relationships.  Thanks for that insight.

[Sean]

You'll need a Calendar Table? Assuming you have one...

Create a Relationship from Date in the Calendar to Date Opened (Active) and then again

from Date in the Calendar to Date Closed (Inactive)

Then create these 4 MEASURES

Opened = COUNTA ( 'Table'[Date Opened] )

Closed =
CALCULATE (
    COUNTA ( 'Table'[Date Closed] ),
    USERELATIONSHIP ( CalendarTable[Date], 'Table'[Date Closed] )
)

Balance = [Opened] - [Closed]

Running Total =
CALCULATE (
    [Balance],
    FILTER (
        ALL ( CalendarTable ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)

Here's the Chart and result...

Hope this helps!

[Anonymous]

This worked well for me!  Thank you so much!