DAX Running Total - One CALCULATE cancels out the other

[CoalesceIsMore]

I’m using the AdventureWorks tabular model for my example. In this example, I’m trying to create a prediction of future sales, using a static range of past data. Then I want a 10-day rolling average of that prediction into the future.

Note: I’m using live connection to Analysis Services, so I can’t create columns or do any modeling. It must all be done in measures.

Here is a view of the resulting data.

Columns 1-3 come straight out of the AdventureWorks model... units sold by date.

Column 4: "Daily Avg from Sample" (this works fine)

Daily Avg from Sample = 
VAR StartDt = DATEVALUE("2014-01-01")
VAR EndDt = DATEVALUE("2014-01-07")
VAR DailyAvgUnits = CALCULATE([Internet Total Units], ALL('Date'[Date]), DATESBETWEEN('Date'[Date], StartDt, EndDt)) / 7
RETURN DailyAvgUnits

Column 5: "Adjust for Sundays" (also works fine)

Adjust for Sundays = 
VAR DayOfWeek = IF(HASONEVALUE('Date'[Day Number of Week]), FIRSTNONBLANK('Date'[Day Number of Week], 'Date'[Day Number of Week]))
VAR DailyAvgUnitsAdjusted = IF(DayOfWeek = 1, [Daily Avg from Sample] * 0.8, [Daily Avg from Sample])
RETURN DailyAvgUnitsAdjusted

Column 6: "Rolling 10 Days" (does not work)

Rolling 10 Days = 
VAR DailyAvgUnitsAdjusted10Day = CALCULATE([Adjust for Sundays], DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -10, DAY))
RETURN DailyAvgUnitsAdjusted10Day

The issue seems to be that I'm defining my original Daily Avg based on the Date column. Then later, I'm defining my Rolling 10 Days also based on the same date column. But I need them both to hold context in their own ways.

[amitchandak · Accepted solution]

@CoalesceIsMore , Try one of the two

Rolling 10 Days =
VAR DailyAvgUnitsAdjusted10Day = CALCULATE([Adjust for Sundays],values(Table[Date]), DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -10, DAY))
RETURN DailyAvgUnitsAdjusted10Day

Rolling 10 Days =
VAR DailyAvgUnitsAdjusted10Day = CALCULATE(AverageX(values(Table[Date]),[Adjust for Sundays]),, DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -10, DAY))
RETURN DailyAvgUnitsAdjusted10Day

[CoalesceIsMore]

Very nice. Your 2nd suggestion yields the correct results.

Am I correct in saying that by adding the AverageX function, we forced a calculation at the correct level? Or is there more to it than that?

I'll paste the final code used here, after just a bit of clean-up.

CALCULATE(AverageX('Date',[Adjust for Sundays]), DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -10, DAY))