cancel
Showing results for 
Search instead for 
Did you mean: 

Rolling Months

Super User
1860 Views
Super User
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!