Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper 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's avatar
    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]
)

     

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hello:

One  way to address is by using DATESINPERIOD but using 28 days. It will work in a weekly grid.
Margin MA 4 weeks = 
var _currentdate = MAX('Date Table'[Date])

IF(
ISBLANK([Margin]),
BLANK(),
CALCULATE([Margin],
AVERAGEX(DATESINPERIOD('Date Table'[Date]), 
_currentday, -28, DAY))

But if you want to do weeks you can have a variable for start week and end week and replace the measure above
var currentweekno = MAX('Date Table'[Week No])
var endweek = currentweekno -4

Margin MA 4 weeks = 
IF(

ISBLANK([Margin]),
BLANK(),
CALCULATE([Margin],
AVERAGEX(DATESBETWEEN('Date Table'[Date]), 
currentweekno, endweek))
 
Lastly if you don't want to start the measure until 4 weeks have passed

VAR daybegin = CALCULATE(FIRSTDATE('Table Date'[Date]), ALLSELECTED('Table Date'[Date]))

VAR add28 = daybegin + 28

VAR _measure = 

CALCULATE([Margin],
AVERAGEX(DATESBETWEEN('Date Table'[Date]), 
currentweekno, endweek))
return

 

IF(MAX('Calendar'[Date Description]) >= add28, _measure, BLANK())

 

I hope this can work for you.



or you can rewrite the bolded part without variables
AVERAGEX(DATESBETWEEN('Date Table'[Date]), 
MAX('Table Date')) - 28, 
MAX('Table Date')))