Forum Discussion
mrbajana
Helper III
Helper IIIRolling Average considering Blank Dates
I have a model with the Fact table and the date dimension. How can I rolling average by n days considering the dates that do not have sales included within a 7-day moving average, for example?
I used this formula but it only consider days with sales
Media Movil 7 D =
VAR LastTransactionDate = MAX(Fecha[Fecha])
VAR AverageDay = 7
VAR PeriodInVisual =
FILTER(
ALL(
Fecha[Fecha]
),
AND(
Fecha[Fecha]> LastTransactionDate - AverageDay,
Fecha[Fecha] <= LastTransactionDate
)
)
VAR OutPut =
CALCULATE(
AVERAGEX(
Fecha,
[Ventas]
),
PeriodInVisual
)
RETURN
OutPut
Try replacing blanks with 0.
Ventas = IF( ISBLANK(SUM([value])), 0, SUM([Value]) )
Hi,
This pattern should work
Measure = divide(calculate([Amount],datesbetween(calendar[date],min(calendar[date])-6,max(calendar[date]))),7)
Hope this helps.
2 Replies
CoreyPSolution Sage
Try replacing blanks with 0.
Ventas = IF( ISBLANK(SUM([value])), 0, SUM([Value]) )
- Ashish_Mathur
Super User
Hi,
This pattern should work
Measure = divide(calculate([Amount],datesbetween(calendar[date],min(calendar[date])-6,max(calendar[date]))),7)
Hope this helps.