Running Total/Average using DATESINPERIOD

Syntax

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

Return Value

• Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals.
• If the number specified for number_of_intervals is positive, dates are moved forward in time; if the number is negative, dates are shifted backward in time.

Scenario 1: Running 7-Day Total

Running 7-Day Total =
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
‘Date’[Date],
MAX(‘Date’[Date]),
-7,
DAY
)
)

Scenario 2: Running 28-Day Total

Running 28-Day Total =
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
‘Date’[Date],
MAX(‘Date’[Date]),
-28,
DAY
)
)

Scenario 3: Rolling Average 7-Day

Rolling 7-Day Average=
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
‘Date’[Date],
MAX(‘Date’[Date]),
-7,
DAY
)
)
/
7

Scenario 4: Rolling Average 7-Day Day over Day (DoD) Difference

This is a tricky one but it’s worth of learning and discussion.

Step 1. Get the “As of Date” Rolling Average 7-Day

Rolling 7-Day Average AOD=
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
‘Date’[Date],
MAX(‘Date’[Date]),
-7,
DAY
)
)
/
7

Step 2. Get the “As of Date -1” Rolling Average 7-Day

Rolling 7-Day Average AOD - 1=
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
‘Date’[Date],
MAX(‘Date’[Date])-1 ,
-7,
DAY
)
)
/
7

Step 3. Get the difference between step 1 & step 2

DoD Rolling 7-Day Average=
Rolling 7-Day Average AOD
- Rolling 7-Day Average AOD-1=