Rolling3months

[wyanjaspew]

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.

[v-saisrao-msft]

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.

[v-saisrao-msft]

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]

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]

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.

[MasonMA · Accepted solution]

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:)