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:

     

     

danrmcallister's avatar
danrmcallister
Resolver II
9 years ago

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's avatar
jsquaredz
Advocate I
9 years ago

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.