Forum Discussion

jsquaredz's avatar
jsquaredz
Advocate I
9 years ago
Solved

Calculate cumulative counts

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 ...
Need Help
  • danrmcallister's avatar
    danrmcallister
    9 years ago

    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:

     

     

Sean's avatar
Sean
Community Champion
9 years ago

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-Aggregation/m-p/40705#M15311

 

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

 

Read Anonymous's posts at the above link! :smileyhappy:

 

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-campbell/

jsquaredz's avatar
jsquaredz
Advocate I
9 years ago

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