Weekly average when daily calculation involves lag (PREVIOUSDAY (or DATEADD('Date', -1, DAY)))

[Anonymous]

I have the following dataset:

dataset

The formula for storage flow is HeldInStorage (day X) + Supply (day X) - Demand (day X) - HeldInStorage (day X - 1).

So for a daily measure, the following DAX code works fine:

Complete = 
VAR StorePrevDay = CALCULATE(SUM(GasBBActualFlowStorage[HeldInStorage]), DATEADD ('Date'[Date], -1, DAY))
VAR StoreDay = 
    SUMX ( 
        'GasBBActualFlowStorage',
        'GasBBActualFlowStorage'[HeldInStorage] + 'GasBBActualFlowStorage'[Supply] - 'GasBBActualFlowStorage'[Demand]
    )
VAR Result = StoreDay - StorePrevDay
RETURN Result

But when I try to get a weekly average, the following formula fails (probably because of wrong filter context, I think)

AvgStorageFlow = 
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR NumberOfDays = 7
VAR PeriodToUse =
    FILTER (
        ALL ('Date'),
        AND (
            'Date'[Date] > LastVisibleDate - NumberOfDays,
            'Date'[Date] <= LastVisibleDate
        )
    )
VAR Result =
    CALCULATE( 
        AVERAGEX( 'Date', [Complete] ),
        PeriodToUse
    )
RETURN Result

What's the DAX code for calculating weekly average of storage flow?

PS: I already generated a Week End Date column in my calendar table with the following:

Week End Date = 
CALCULATE ( 
    MAX ('Date'[Date] ),
    ALLEXCEPT ('Date','Date'[Week Number],'Date'[Year] )
)

@dax 

[daXtreme · Accepted solution]

Hi @Anonymous 

What exactly does it mean "the formula fails"? You have not shown any output. At first glance the formulas seem to be OK but without seeing the wrong results and the expected output it's not possible to diagnose.

[Anonymous]

When I prepared the results to answer you, I noticed the results were fine.

I must have made a mistake when checking the first time.

Cheers

results match

[SpartaBI]

@Anonymous hard to say, need more info. If you want I'm available now for a quick zoom call to look together