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([Margin]),
BLANK(),
CALCULATE(
AVERAGEX(
VALUES('Date Table'[Week Rank]),
Main_Table[Margin]
),
FILTER(
ALL('Date Table'),
'Date Table'[Week Rank] >= MAX('Date Table'[Week Rank])-4 &&
'Date Table'[Week Rank] <= MAX('Date Table'[Week Rank])
)
)
)
 
 
Week Rank is calculated column in our Date Table
Week Rank =
RANKX(
ALL('Date Table'),
'Date Table'[Week Start Date],,
ASC,
DENSE
)
 
Date Table is as below
Date Table =
VAR MinDate = YEAR( MIN ( Main_table[Invoice Date] ) )
VAR MaxDate = YEAR( MAX ( Main_table[Invoice Date] ) )

RETURN
ADDCOLUMNS(
FILTER(
CALENDARAUTO(),
YEAR([Date]) >= MinDate &&
YEAR([Date]) <= MaxDate
),
"Year", YEAR([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Month Number", FORMAT([Date], "mm"),
"Week Number", WEEKNUM([Date],2),
"Week Day Number", WEEKDAY([Date],2),
"Week Day Name", FORMAT([Date],"dddd"),
"Month Year", FORMAT([Date], "MMMM YYYY"),
"Month Year Code", FORMAT([Date], "YYYYMM"),
"Week Start Date", [Date] - WEEKDAY([Date],2)+1,
"Week End Date", [Date] - WEEKDAY([Date],2)+7
)
 
We have Dates from 1st Jan 2021 to 31st Dec 2022.
 
Snapshot from Date Table

 

Can someone help me on this. Is this any DAX issue.
 
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]
)

     

11 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution 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')))
  • littlemojopuppy's avatar
    littlemojopuppy
    Community 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]
)
    • apatwal's avatar
      apatwal
      Helper III
      4 years ago

      Hi littlemojopuppy 

       

      Your DAX is also giving me same result; there is no change is result.
      Could you help me on this?

       

      Hi Whitewater100 

      Looks like there are issues in your DAX, I could not understand...

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

    • Whitewater100's avatar
      Whitewater100
      Solution Sage
      4 years ago

      I had decided not to reply earlier but then you are demonstrating today what you condemed yesterday?

       

      So, yesterday you wrote:

      "you posted your response an hour after I did.  I've noticed you doing this on multiple posts in the last couple weeks, both to me and to others.  There's an expectation of etiquette in the forum, where we try not to overwhelm the users with numerous responses.  Because all it does is create confusion.  It would be appreciated if you followed etiquette.

       

      Then today your reply today (I'm meassge #2 and you are message#3,came after my reply on this topic. My reply was correct.  If you read it you would know this question was answered to the point of a minor tweak ,based on the users reply.  And to avoid confusion I let you run with it for many replies.

      The point here is that on occasion, multiple people may be wokring on a solution for others. After spending time on a working solution, it is not always realistic to read or even see others replies. As I explained earlier to you, that none of this is purposeful or ego driven on my side. I also believe the question when solved is noted but until then others can try to help. Some questions have many replies by more than one person in the spirit of aiding another.

       

       I have seen you jump in on others replies,  but did't think this behavior correction should be aired or corrected openly, like you choose to do. Then the very next day you do the same with me on this topic.

       

      The goal here is to help others, offer sugestions and I'm sorry if you feel you are the only one who can participate once you decide you want to reply.

       

      You are the only one here pointing out your issues.