Dax for Last 6 months Rolling average

[manojbariki1987]

Hi Everyone,

Could anyone please help me with Dax for the last 6 months rolling average and it has to be dynamic as selection with months.

Early responses are highly appreciated.

Thanks & Regards

Manoj

[v-xiandat-msft · Accepted solution]

Hi @manojbariki1987 ,

Using Calculation Groups:

• Calculation groups allow you to dynamically switch measures based on slicer selections. In your case, we’ll create a calculation group to handle the rolling average.

• Here are the steps:

  • Create a New Table:

    • First, create a new table called “Previous Date.” This table will be independent of slicer filters.
    • You can copy the existing Date table to create this new table: .Previous Date = 'Date' Copy

  • Link the Tables:

    • Create an inactive relationship between the Date table and the Previous Date table. This ensures that the filter won’t propagate from the slicer to the Previous Date table.

  • Write a DAX Measure:

    • Define a measure that calculates the rolling average for the last 6 months.
    • For example, if your measure is called “Sales Amount,” the DAX code might look like this:

Rolling Average = 
VAR SelectedDate = MAX('Date'[Date])
VAR RollingMonths = DATESINPERIOD('Previous Date'[Date], LASTDATE('Date'[Date]), -6, MONTH)
VAR AvgSales = AVERAGEX(RollingMonths, [Sales Amount])
RETURN IF(HASONEVALUE('Date'[Month]), AvgSales, BLANK())

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel29195]

@manojbariki1987 

create w dimdate table . 

link it to your fact. 

check this article. would help you . 

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

let me know this works for you. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠