topic Measure for average of last seven day totals in DAX Commands and Tips

Measure for average of last seven day totals

greenskmachine2 — Tue, 10 Dec 2024 19:32:40 GMT

Hi there,

I am trying to calculate a measure for the average total, by date, over the past seven dates. I want this measure to still give me the correct seven day average no matter what date slicers are chosen.

Below is my workings, but it seems to just give me the total for that day, and not the average total of the past seven days.
I have a custom date table sitting over my date table, hence 'custom date'.

Any help to sort this out would be appreciated:

7 Day Average =

var _maxDate = LASTDATE(Date[Calendar_Date])

VAR DailySums =

SUMMARIZE(

Sales,

Date[Calendar_Date],

"DailySum", SUM(Sales[Volume])

)

RETURN

CALCULATE(AVERAGEX(DailySums, [DailySum]),
DATESBETWEEN(Date[Calendar_Date],DATEADD(_maxDate,-6,DAY),_maxDate),ALL('Custom Date'))

Re: Measure for average of last seven day totals

Semaj06Fr — Tue, 10 Dec 2024 20:16:21 GMT

HI,

Please try this code :

var _maxDate = LASTDATE(Date[Calendar_Date])

var min_Date=_maxDate-6

var result=averagex(

ADDCOLUMNS(

filter(

SUMMARIZE(DailySums,DailySums[Calendar day]),DailySums[Calendar day]>=FD&&DailySums[Calendar day]<=LD),

"@Sales",[DailySum]),

[@sales])

return

result

Hope it helps

James

Re: Measure for average of last seven day totals

Anonymous — Wed, 11 Dec 2024 10:00:47 GMT

Hi greenskmachine2 ,

This is because the context of Date[Calendar_Date] hasn't be removed and you can try this method.

7 Day Average = var _maxDate = LASTDATE(CalendarTable[Date]) VAR _minDate = _maxDate-7 RETURN CALCULATE( AVERAGE(Sales[Volume]),'Sales'[Date]>=_minDate&&'Sales'[Date]<=_maxDate,ALL('Sales'))

Best regards,

Mengmeng Li

Re: Measure for average of last seven day totals

johnt75 — Wed, 11 Dec 2024 10:34:34 GMT

Try

7 Day Average = VAR MaxDate = MAX ( 'Date'[Calendar_Date] ) VAR Result = AVERAGEX ( DATESINPERIOD ( 'Date'[Calendar_Date], MaxDate, -7, DAY ), CALCULATE ( SUM ( Sales[Volume] ), REMOVEFILTERS ( 'Custom Date' ) ) ) RETURN Result