Rolling Months 04-06-2018 16:38 PM - last edited 06-27-2018 10:40 AM

[Greg_Deckler]

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

eyJrIjoiYjQzMjdiZTQtMTNiNC00YTI4LWEyZmUtNTcxYjlhZTg3YTA4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9