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

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:

Anonymous's avatar
Anonymous
Not applicable
3 years ago

This worked well for me!  Thank you so much!