Cumulative Count formula irrespective of dates

[avalerion]

Having tried a few of the solutions posted around the web, I can't seem to figure out how to calculate a simple cumulative count.

In Excel, this would simply look like: =countif(A:A,"<="&date(2015,1,31)

The end result would provide a cumulative count as each month comes along.

However in my PowerBI model, there's a table with the 'account_creation_date' and another table acting as a master date table 'all_dates'. No relationship between the two since that would create some challenges.

So the question is how to create a formula that looks at ALL rows in the 'account' table and returns the cumulative count for that date where 'account_creation_date' = 'all_date'?

[Twan · Accepted solution]

See if this works.  You just need to change the [Date] columns to the correct name of the date columns in your model.

Cumulative Count = CALCULATE(COUNTROWS('account_creation_date'), FILTER('account_creation_date', 'account_creation_date'[Date] <= MAX('all_dates'[Date])))

The equation would also have to change if you ended up creating a relationship between the 'account_creation_date' and 'all_dates' table.

[Nickodemus]

Hi @Twan - I've also used this solution successfully, but please can you elaborate on how I'd need to change the measure if I have a relationship between the 'account_creation_date' and 'all_dates' table? I had a relationship which made some of my other visualisations work well, but stopped your formula working. If i deactivated the relationship, your formula worked well, but my other charts are now broken....

Many thanks.

[Twan]

If you have a relationship between 'account_creation_date'[Date] and 'all_dates'[Date] then the formula should look like the below equation.  Also, DAX Patterns has a good summary of how this type of measure works.

Cumulative Count =
CALCULATE( COUNTROWS('account_creation_date'),
    FILTER( ALL('all_dates'[Date]),
        'all_dates'[Date] <= MAX( 'all_dates'[Date] )
    )
)

[avalerion]

That worked - thanks! I realized that there was another filter that I needed to append as well which solved the issue.