Forum Discussion
Helper IRolling sum between relative dates?
Hello! I am trying to get a rolling sum between two dates. Normally I would use something like the statement below to just get for example the last 365 days:
Revenue Prior 365 Days= Calculate(SUM(tblSummary[Revenue]),
DATESINPERIOD(tblSummary[ReportDate],
LASTDATE(tblSummary[ReportDate]),-7,DAY))
But, now I need to get a rolling sum for prior 365 before the current prior 365 days... if that makes sense.... Just not sure how to work a second filter in there so that i can get a rolling date period where one of the dates isnt just the last date in the report.
Thanks in advance!
5 Replies
- some_bih
Community Champion
Hi OhLookAnError please describe "how" you model you inputs for this wanted output, like do you use slicer of you just want measure depending on ...is 365 fixed all time or this is just year...if possible share model to better understand your model
OhLookAnErrorHi, it is just going in a table. So for example, the first column would show the sum of the last 90 days and the next column would show the sum of the 90 days before that.
- some_bih
Hi OhLookAnError possible solution as measure
Running sum test =
CALCULATE (
SUM(tblSummary[Revenue]),
DATESINPERIOD (
'Date'[Date], --this column must be Calendar / Date table in Power BI
MAX ( 'Date'[Date] ),--this column must be Calendar / Date table in Power BI
-1, --change as needed
YEAR --period could Day, Month, Quarter, Year
)
)
