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

     

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
4 years ago

apatwal there's no need for the CALCULATE function at all.  Just AVERAGEX

apatwal's avatar
apatwal
Icon for Helper III rankHelper III
4 years ago

Hi littlemojopuppy 

 

Its now giving me nothing...

using below DAX

Margin MA 4 weeks =
var _currentdate = MAX('Date Table'[Date])
RETURN
AVERAGEX(
DATESINPERIOD(
'Date Table'[Date],
-_currentdate,
-28,DAY
),
[Margin]
)
 

 

and using below DAX, I am getting wrong values...

4 week Rolling Avg =
VAR RelevantWeeks =
    CALCULATETABLE(
        VALUES('Date Table'[Week End Date]),
        DATESBETWEEN(
            'Date Table'[Date],
            MAX('Date Table'[Date]) - 28,
            MAX('Date Table'[Date])
        )
    )
RETURN

AVERAGEX(
    RelevantWeeks,
    [Margin]
)
 

 

 

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    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]
)