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)
Solution SageYou don't need to buid a separeate table but can do it with Time Inteligence. So you need to have a date table. And then have a Measure that calculates the last 7,21, 30 or whatever period you want to sum over.
Here is an example for the last 7 Days - https://community.powerbi.com/t5/Desktop/Measure-for-count-of-last-7-days/td-p/106692
you can just replace SUM vs the Distinct Count.
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.
- Seward12533
Time Intelligence functions need date tables. For this work you need a date table that is linked to your data and then you compare the date in your table to date in the date table.
2daysum = CALCULATE(
SUM(Sheet1[Sold]),
FILTER(ALL('DateDim'), 'Sheet1'[Date] >= DateDim[Date]-2 && Sheet1[Date]<DateDim[Date])
)Hmm, I am still getting blank values after creating the Date Dimension table and linking it to the Date column in my dataset.
My Date Dimension table is just unique dates between the minimum date and maximum date within my original dataset.
2daysum = CALCULATE(
SUM(Sheet1[Sold]),
FILTER(ALL('DateTable'),'Sheet1'[Date]>=DateTable[Date]-2 && Sheet1[Date]<DateTable[Date]
))
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!
