Rolling3months

Could you help me configure this? I'm trying to display the rolling 3-month period using the following DAX code.

Sample dataset

I’d like the output to look like this.

Hello wyanjaspew

I would first create one 'DateTable', properly mark it as a Date table in Power BI and use it to filter the fact table by 'one to many' relationship. You can either create Date table in Power Query or with DAX in Power BI. Below is the one with DAX.

DateTable = 
ADDCOLUMNS(
    CALENDAR(
        DATE(2025,1,1),
        DATE(2025,12,31)
    ),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "YearMonth", FORMAT([Date], "yyyy-MM")
)

After this, use below Measure for 'Rolling_3_Month' calculation (assuming your Total Service ID = DISTINCTCOUNT(test[ServiceID]) )

Rolling_3_Months = 
VAR _currentDate = MAX(DateTable[Date])
VAR _rollingPeriod = 
    DATESINPERIOD(
        DateTable[Date],
        _currentDate,
        -3,
        MONTH
    )
VAR _result=
        CALCULATE(
            AVERAGEX(
                VALUES(DateTable[MonthName]), 
                [Total Service ID]
            ),
            _rollingPeriod
        )

RETURN
    IF(
        ISBLANK([Total Service ID]),
        BLANK(),
        _result
        )

With small portion of sample data

Hope this helps:)

5 Replies

v-saisrao-msft
Community Support
1 year ago
Hi wyanjaspew,

Thank you MasonMA, for your insights.

I’ve reproduced your issue using a sample dataset and got the below output:

I’ve also attached the PBIX file for your reference.

Hope this helps

Thank you.
4792520_Rolling.pbix42 KB
v-saisrao-msft
Community Support
1 year ago
Hi wyanjaspew,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.
v-saisrao-msft
Community Support
11 months ago
Hi wyanjaspew,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.
v-saisrao-msft
Community Support
11 months ago
Hi wyanjaspew,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

MasonMA

Super User