Rolling X is a useful feature that enables you to calculate the cumulative total or average of a specific metric over a defined period of time. It is commonly used in financial analysis, sales forecasting, and other data-driven processes.<\/p>\n\n\n\n
For example, if X=3, rolling 3 months of sales will be the sum or average of the sales for the past three months, and this period will update each month to include the most recent three months.<\/p>\n\n\n\n
In this article, I will walk you through different ways to calculate rolling X months in Power BI, using DAX formulas.<\/p>\n\n\n\n
Read to learn more.<\/p>\n\n\n\n
To calculate rolling X months in Power BI, you need a date column that contains the dates that you want to calculate. Once you have your dates table set up, you can do some great data-based analysis.<\/p>\n\n\n\n
Also, you must create a measure for the value you want to calculate using any aggregation function such as In Power BI, you can calculate rolling X months in different ways, using different functions such as the You can use the The first thing to do is to create a measure for the value you want to calculate. This measure will be used to calculate the rolling X months value. For example, if you want to calculate the rolling X months average, you will use the After that, create a measure for the rolling X months. To do that, use the following DAX formula for the measure you created for the rolling X months.<\/p>\n\n\n\n Replace You can also use the Create a measure for the value you want to calculate (just like I did earlier). Then create a measure for the rolling X months using the following DAX formula. Use the following DAX formula:<\/p>\n\n\n\n Replace The first line defines a variable called CurrentDate<\/strong>. You can replace it in this formula with any date column or variable that contains the current date you want to use as a reference point for the rolling X months period.<\/p>\n\n\n\n NB: The <\/em> To use the Replace Finally, you can use the Create a measure for the value you want to calculate, and then create a measure for the rolling X months using the following DAX formula:<\/p>\n\n\n\nSUM, AVERAGE, MIN, MAX<\/strong><\/code>, etc. You will use this measure to calculate the rolling X months value.<\/p>\n\n\n\nDATESINPERIOD, EARLIER, RANKX, DATESBETWEEN<\/strong><\/code> functions, etc. I will explain each of them.<\/p>\n\n\n\n1. Using the DATESINPERIOD function<\/h2>\n\n\n\n
DATESINPERIOD <\/strong><\/code>function to calculate rolling X months by following the steps below:<\/p>\n\n\n\nAVERAGE<\/code> <\/strong>function.<\/p>\n\n\n\nAverage_Sales = AVERAGE(SalesTable[SalesAmount])<\/strong><\/code><\/pre>\n\n\n\nRolling X Months = \nCALCULATE (\n [Average_Sales],\n DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), X, MONTH )\n)<\/code><\/strong><\/pre>\n\n\n\n[Average_Sales]<\/strong><\/code> with the name of the measure you created in the first step (I used the one I created in this example). Replace X <\/strong>with the number of months you want to include in the rolling calculation.<\/p>\n\n\n\n2. Using the EARLIER function<\/h2>\n\n\n\n
EARLIER <\/strong><\/code>function to create a rolling X months calculation. To do that, follow the below steps:<\/p>\n\n\n\nRolling X Months =\nVAR CurrentDate = MAX ( 'Date'[Date] )\nRETURN\n CALCULATE (\n [Your Measure],\n FILTER (\n ALL ( 'Date' ),\n 'Date'[Date] >= DATEADD ( CurrentDate, -X, MONTH )\n && 'Date'[Date] <= CurrentDate\n )\n )<\/code><\/strong><\/pre>\n\n\n\n[Your Measure]<\/code> with the name of the measure you created in step 1, and replace X <\/strong>with the number of months you want to include in the rolling calculation.<\/p>\n\n\n\nEARLIER <\/strong><\/code>function is not used in the above formula, but can be used in a similar context to reference a previous value.<\/em><\/p>\n\n\n\n3. Using the DATESBETWEEN function<\/h2>\n\n\n\n
DATESBETWEEN <\/strong><\/code>function, create a measure for the value you want to calculate, and create a measure for the rolling X months using the following DAX formula:<\/p>\n\n\n\nRolling X Months = \nCALCULATE (\n [Your Measure],\n DATESBETWEEN (\n 'Date'[Date],\n DATEADD ( LASTDATE ( 'Date'[Date] ), -X, MONTH ),\n LASTDATE ( 'Date'[Date] )\n )\n)<\/code><\/strong><\/pre>\n\n\n\n[Your Measure]<\/strong><\/code> with the name of the measure you created in step 2, and replace X<\/strong><\/code> with the number of months you want to include in the rolling X.<\/p>\n\n\n\n4. Using the RANKX function<\/h2>\n\n\n\n
RANKX <\/strong><\/code>function to create a rolling X months calculation through the following steps:<\/p>\n\n\n\nRolling X Months =\nVAR SelectedDate = MAX ( 'Date'[Date] )\nVAR DatesRanked = RANKX ( ALL ( 'Date' ), 'Date'[Date],, ASC )\nVAR RollingDates =\n FILTER (\n ALLSELECTED ( 'Date' ),\n DatesRanked >= DatesRanked - X\n && DatesRanked <= DatesRanked\n )\nRETURN\n CALCULATE (\n [Your Measure],\n 'Date'[Date] IN RollingDates, \n ALLSELECTED ( 'Date' )\n )<\/code><\/strong><\/pre>\n\n\n\n