Forum Discussion

Helper III

4 years ago

Solved

Issue in Rolling 4 Week Average

Hi, I am facing calculation issue in Rolling 4 Week Average; Margin (Measure) Rolling 4 Wk Average (Measure) Please find below DAX used: Rolling 4 Wk Average= IF( ISBLANK...

DAX

DAX Commands and Tips

Help required

littlemojopuppy

4 years ago

Hi apatwal

Here's the answer

Margin 4 Week Rolling Average = 
VAR	RelevantWeeks =
	CALCULATETABLE(
		VALUES('Calendar'[WeekEndingDate]),
		DATESBETWEEN(
			'Calendar'[Date],
			MAX('Calendar'[Date]) - 28,
			MAX('Calendar'[Date])
		),
        REMOVEFILTERS('Calendar')
	)
RETURN

AVERAGEX(
	RelevantWeeks,
    [Total Margin]
)

Rolling Average.pbix57 KB

littlemojopuppy

Community Champion

4 years ago

Hi apatwal I'm not understanding why you're ranking weeks as part of this calculation. If you have a date table (and you do) and it is marked as one, your calculation should be fairly simple unless I'm missing something?

VAR	RelevantWeeks =
	CALCULATETABLE(
		VALUES(DateTable[Week End Date]),
		DATESBETWEEN(
			DateTable[Date],
			MAX(DateTable[Date]) - 28,
			MAX(DateTable[Date])
		)
	)
RETURN

AVERAGEX(
	RelevantWeeks,
	[Margin]
)