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

    • apatwal's avatar
      apatwal
      Helper 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
        Community 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]
)