Forum Discussion
Transaction level date to Rolling Time Summary
Hmm, not sure if you want to get the following results.
If it is, first create a calcuated column and use the following DAX to add Rank Number
Rank = RANKX(Sold,Sold[Date],,ASC,Dense)
Then, create another calucated column and use the DAX below to get the cumulative sum in 2 days by item name
2Days_Sum = CALCULATE(SUM(Sold[Sold]),ALLEXCEPT(Sold,Sold[Item Name]),Sold[Rank]<=EARLIER(Sold[Rank])&&Sold[Rank]>EARLIER(Sold[Rank])-2)
Sorry I did a poor job explaining my problem. I tried to follow the solution you linked too and I ran into the same problem I've been having.
I don't want to look at the latest time frame but at 4 days from the date within the row.
I've been trying to apply time intelligence methods to the query but I keep running into the same issue.
Here is an example of a two day rolling sum.
2daysum = CALCULATE(
SUM(Sheet1[Sold]),
FILTER(ALL('Sheet1'), 'Sheet1'[Date] >= Sheet1[Date]-2 && Sheet1[Date]<Sheet1[Date])
)
So I want to sum everything between the date 2 days ago and the current date in the row.
When I do this I just recieve blank results for every row.
Hmm, not sure if you want to get the following results.
If it is, first create a calcuated column and use the following DAX to add Rank Number
Rank = RANKX(Sold,Sold[Date],,ASC,Dense)
Then, create another calucated column and use the DAX below to get the cumulative sum in 2 days by item name
2Days_Sum = CALCULATE(SUM(Sold[Sold]),ALLEXCEPT(Sold,Sold[Item Name]),Sold[Rank]<=EARLIER(Sold[Rank])&&Sold[Rank]>EARLIER(Sold[Rank])-2)
This is exactly what I was looking for. Thank you!