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

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

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

  • apatwal's avatar
    apatwal
    Icon for Helper III rankHelper 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
      Icon for Community Champion rankCommunity 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
        Icon for Helper III rankHelper III
        4 years ago

        Hi littlemojopuppy 

         

        Thanks for your reply!

        Ooops that was my small mistake..

        But Now I am facing another issue