Forum Discussion
How do I groupby all previous rows
Hello,
I've a table with customer, count and date as columns and what I want to plot is a trend chart of the count vs. date. If I directly plot the trend with the data shown below then I wouldn't get accurate results.
| Customer | count | date |
| A | 1 | 1/1/2019 |
| B | 3 | 1/1/2019 |
| C | 2 | 1/3/2019 |
| A | 2 | 1/5/2019 |
| B | 6 | 1/6/2019 |
| A | 0 | 1/10/2019 |
Is there a way to groupby on dates and pick the latest "count" for every customer until the selected date.
What I want is
| date | total count |
| 1/1/2019 | 4 (A:1, B:3) |
| 1/3/2019 | 6 (A:1, B:3, C:2) |
| 1/5/2019 | 7 (A:2, B:3, C:2) |
| 1/6/2019 | 10 (A:2, B:6, C:2) |
| 1/10/2019 | 8 (A:0, B:6, C:2) |
Any help would be appreciated, thanks.
2 Replies
Here are a couple of slightly different approaches:
RollingCount = VAR LastDates = SUMMARIZE ( FILTER ( ALLSELECTED ( Customers ), Customers[date] <= MAX ( Customers[date] ) ), Customers[Customer], "@LastDate", MAX ( Customers[date] ) ) VAR LastCounts = ADDCOLUMNS ( LastDates, "@LastCount", CALCULATE ( SUM ( Customers[count] ), Customers[date] = EARLIER ( [@LastDate] ) ) ) RETURN SUMX ( LastCounts, [@LastCount] )Rolling Count = SUMX ( ALLSELECTED ( Customers[Customer] ), VAR CurrDate = MAX ( Customers[date] ) VAR LastCustDate = CALCULATE ( MAX ( Customers[date] ), Customers[date] <= CurrDate ) VAR LastCustCount = CALCULATE ( SUM ( Customers[count] ), Customers[date] = LastCustDate ) RETURN LastCustCount )
