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?

    • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I have been looking for this solution for days!! This is so good. Thank you!!!!!