Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |