cancel
Showing results for
Search instead for
Did you mean:

# Rolling Months

Super User
2031 Views
Highlighted
Super User

## Rolling Months

There are a number of business analysis calculations that require analyzing a sliding scale of previous (complete) months' data. For example, in certain forecasting scenarios it is fairly common that the last 3 complete months of data is used to forecast the current month. For example, if today is April 6th, 2018, then the dates desired would be from January 1st, 2018 to March 31st, 2018. These sliding scales must be able to cross year boundaries as well as ensure that the date ranges calculated are exact. Being off even a single day can sometimes greatly affect forecasts.

These two measures calculate the beginning and ending dates for a sliding scale of some number of months. The starting and ending points can be edited quite easily in formulas.

Start Date

```StartDate =
VAR DateFrom = TODAY()
VAR numMonthsBack = 3
VAR StartDate = EOMONTH(DateFrom,(numMonthsBack+1)*-1)+1
RETURN StartDate```

End Date

```EndDate =
VAR DateFrom = TODAY()
VAR numMonthsBack = 1
VAR EndDate = EOMONTH(DateFrom,numMonthsBack*-1)
RETURN EndDate```

Edit 4/12/17

A variation on this would be:

Start Date

```StartDate =
VAR DateFrom = MAX(Table[Date])VAR numMonthsBack = 3
VAR StartDate = EOMONTH(DateFrom,(numMonthsBack+1)*-1)+1
RETURN StartDate```

End Date

```EndDate =
VAR DateFrom = MAX(Table[Date])VAR numMonthsBack = 1
VAR EndDate = EOMONTH(DateFrom,numMonthsBack*-1)
RETURN EndDate```

This would allow you to use the measure in a visual context such as a Line Chart, Bar Chart, etc. that has a date dimension as an axis.

To use this in a measure to calculate some aggregation (in the example the column [Value], use a measure like this:

```Measure =
VAR rollmonthstart = [Month Start]
VAR rollmonthend = [Month End]
VAR tmpTable = ALL('Calendar')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollmonthstart&&[Date]<=rollmonthend)
RETURN AVERAGEX(tmpTable1,[Value])```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!