SAMEPERIODLASTYEAR with rolling sum

Hi wynhodgkiss ,

 

This is a pretty common scenario when working with rolling periods and year-on-year comparisons in Power BI. The trick is to make sure you only calculate the prior year rolling sum (e.g., MAT, RQ, etc.) when all the months in that prior year window have data. Otherwise, you risk showing misleading numbers due to missing months. Here’s how you can do it for MAT (12 months), but you can adjust the window for RQ or other periods as needed:

 

SalesFinal_PY_MAT =
VAR SelectedDate = MAX(DatesTable[Month])
VAR PY_Window =
    DATESINPERIOD(DatesTable[Month], EDATE(SelectedDate, -12), 12, MONTH)
VAR AllMonthsPresent =
    CALCULATE(
        COUNTROWS(DatesTable),
        PY_Window
    ) = 12 // Change 12 to 3 for rolling quarter, etc.
RETURN
IF(
    AllMonthsPresent,
    CALCULATE([SalesFinal], PY_Window),
    BLANK()
)

 

This checks if there are 12 months of data in the prior year window, and only returns a value if all are present otherwise, it stays blank until you have a full set. You can apply the same pattern for rolling quarters or custom periods just update the window size.