Forum Discussion

apatwal's avatar
apatwal
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'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
Community Champion
4 years ago

Hi apatwal what's the issue?  You never said what's incorrect?

apatwal's avatar
apatwal
Helper III
4 years ago

Hi littlemojopuppy 

 

Sorry I missed out the screenshots.

 

Looks like may be I am missing something:

 

 

 

 

Thanks...

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    Hi apatwal you're not having it average anything.  After the closing parenthesis for DATESBETWEEN, add a comma and then [Margin] (or the name of your measure).  That should take care of it

    • apatwal's avatar
      apatwal
      Helper III
      4 years ago

      Hi littlemojopuppy 

       

      Thanks for your reply!

      Ooops that was my small mistake..

      But Now I am facing another issue

       

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        4 years ago

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