Measure for average of last seven day totals

[greenskmachine2]

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

[johnt75]

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

[Anonymous · Accepted solution]

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

[Semaj06Fr]

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