topic Re: Rolling3months in DAX Commands and Tips

Rolling3months

wyanjaspew — Mon, 11 Aug 2025 00:37:47 GMT

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.

Re: Rolling3months

MasonMA — Mon, 11 Aug 2025 03:43:26 GMT

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