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 ge...
Great! This works, thanks for the solution!
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
)
Great! This works, thanks for the solution!