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

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:

 

 

jsquaredz's avatar
jsquaredz
Advocate I
9 years ago

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?