The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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'?
Solved! Go to 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.
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.
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.
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] ) ) )
That worked - thanks! I realized that there was another filter that I needed to append as well which solved the issue.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |