Forum Discussion
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 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
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:
9 Replies
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:
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?
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:
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! :smileyhappy:
