Issue in Rolling 4 Week Average

[apatwal]

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.

[littlemojopuppy]

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

[Whitewater100]

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.

[littlemojopuppy]

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

[apatwal]

Hi @littlemojopuppy 

Sorry I missed out the screenshots.

Looks like may be I am missing something:

Thanks...

[littlemojopuppy]

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]

Hi @littlemojopuppy 

Thanks for your reply!

Ooops that was my small mistake..

But Now I am facing another issue

[littlemojopuppy]

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

[apatwal]

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 · Accepted solution]

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

[apatwal]

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

[Whitewater100]

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