Forum Discussion
Cumulative count with several entries for same date
Hello Power BI geniuses (and people with the same issue as me...)
I am trying to write a measure to give me a cumulative count over a period of time, using this:
Cumulative stuff = CALCULATE(COUNTA('My Table'[stuff I want to count]),FILTER('My Table'),('DateTable'[Date])<=today()))
The problem is, in My Table, I can have more than one row for a single date:
| My Table - Date | Stuff i want to count |
| 28-Dec-17 | A |
| 01-Feb-18 | B |
| 22-Feb-18 | C |
| 07-Mar-18 | D |
| 09-Mar-18 | E |
| 10-Mar-18 | F |
| 11-Mar-18 | G |
| 11-Mar-18 | H |
| 15-Mar-18 | I |
| 15-Mar-18 | J |
And I get an error message telling me "a single value for the column date cannot be determined..."
I've tried grouping my table per date and and count of rows, but it doesn't seem to solve the issue.
My date table was creating using Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31)) AND i manually checked, I don't have any duplicates in that table :/
Can anybody suggest a solution?
Thanks in advance :D
2 Replies
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
Please refer to this measure:
Cumulative stuff = CALCULATE ( COUNT ( 'My Table'[Stuff] ), FILTER ( ALL ( 'My Table' ), 'My Table'[Date] <= TODAY () ) )Best regards,
Yuliana Gu
Thank you sooooo much for taking the time (I'm going crazy) - the error message is gone but I must be doing something wrong because it gives me the same value for each date (looks like it's the total count of [stuff]). How do I get it to give me the cumulative count for each day?
Initial table
My Table - Date Stuff 28-Dec-17 A 01-Feb-18 B 22-Feb-18 C 07-Mar-18 D 09-Mar-18 E 10-Mar-18 F 11-Mar-18 G 11-Mar-18 H 15-Mar-18 I 15-Mar-18 J What I see now:
My Table - Date Cumulative count 28-Dec-17 10 01-Feb-18 10 22-Feb-18 10 07-Mar-18 10 09-Mar-18 10 10-Mar-18 10 11-Mar-18 10 15-Mar-18 10 What I'd like to see:
My Table - Date Cumulative count 28-Dec-17 1 01-Feb-18 2 22-Feb-18 3 07-Mar-18 4 09-Mar-18 5 10-Mar-18 6 11-Mar-18 8 15-Mar-18 10