topic Re: Rolling sum between relative dates? in DAX Commands and Tips

Rolling sum between relative dates?

OhLookAnError — Mon, 30 Oct 2023 14:35:49 GMT

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!

Re: Rolling sum between relative dates?

some_bih — Tue, 31 Oct 2023 08:00:47 GMT

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

Re: Rolling sum between relative dates?

OhLookAnError — Tue, 31 Oct 2023 12:35:07 GMT

Hi, 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.

Re: Rolling sum between relative dates?

Dangar332 — Tue, 31 Oct 2023 13:41:07 GMT

Hi, OhLookAnError

OhLookAnError

try below

just adjust your column name

rolling sum for 90 days

365 Days = Calculate( SUM(tblSummary[Revenue]), tblSummary[ReportDate]> max(tblSummary[ReportDate])-90 && tblSummary[ReportDate]<=max(tblSummary[ReportDate] )

rolling sum for 365 days

365 Days = Calculate( SUM(tblSummary[Revenue]), tblSummary[ReportDate]> max(tblSummary[ReportDate])-365 && tblSummary[ReportDate]<=max(tblSummary[ReportDate] )

Re: Rolling sum between relative dates?

some_bih — Tue, 31 Oct 2023 13:31:02 GMT

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
)
)

Re: Rolling sum between relative dates?

OhLookAnError — Wed, 15 Nov 2023 15:42:13 GMT

I got this below to work but is there a way to do it in months instead of days?

PreviousPeriod = Calculate(SUM('tbl'[Revenue]),

DATESBETWEEN('Calendar'[Date],

MAX('Calendar'[Date]) - 180,

MAX('Calendar'[Date]) -90))