How to Calculate Rolling Average Sales using DAX in Power BI

How to use DATESINPERIOD Function to calculate Rolling 12 Months Average Sales in Power BI

Scenario:
Let's suppose we have a Data Model as follows with Orders and Sales details.

From this Model, we can calculate the Rolling 12 Months Sales and Average Sales using the below DAX Expressions.

Sales Rolling12M_Total =
VAR NumOfMonths=12
VAR LastSelectDate=MAX('tbl_Calendar'[cDate])

VAR PeriodRange=DATESINPERIOD('tbl_Calendar'[cDate], LastSelectDate,-NumOfMonths,MONTH)

VAR Result=CALCULATE(
                        SUMX(VALUES(tbl_Calendar[Month_No]), [Total Sales]), 

                            PeriodRange

                                )

VAR LastDateOfSales=MAX(tbl_OrderDetails[Order_Date])
VAR FirstSelectedDate=MIN('tbl_Calendar'[cDate])

RETURN
IF(FirstSelectedDate<=LastDateOfSales,Result)

Sales Rolling12M_Avg =

VAR NumOfMonths=12
VAR LastSelectDate=MAX('tbl_Calendar'[cDate])

VAR PeriodRange=DATESINPERIOD('tbl_Calendar'[cDate], LastSelectDate,-NumOfMonths,MONTH)

VAR Result=CALCULATE(
                        AVERAGEX(VALUES(tbl_Calendar[Month_No]), [Total Sales]), 

                            PeriodRange

                            )

VAR LastDateOfSales=MAX(tbl_OrderDetails[Order_Date])
VAR FirstSelectedDate=MIN('tbl_Calendar'[cDate])

RETURN
IF(FirstSelectedDate<=LastDateOfSales,Result)

Notes:

LastDateOfSales - is the last date for which the Sales is available

FirstSelectedDate - is the first visible date of each Selected Date/Month in the Visual.

The condition IF(FirstSelectedDate<=LastDateOfSales,Result) will ensures the result for only the available Sales dates.

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------