Rolling Average considering Blank Dates

[mrbajana]

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

[CoreyP · Accepted solution]

Try replacing blanks with 0.

Ventas = IF( ISBLANK(SUM([value])), 0, SUM([Value]) )

[Ashish_Mathur · Accepted solution]

Hi,

This pattern should work

Measure = divide(calculate([Amount],datesbetween(calendar[date],min(calendar[date])-6,max(calendar[date]))),7)

Hope this helps.

[CoreyP · Accepted solution]

Try replacing blanks with 0.

Ventas = IF( ISBLANK(SUM([value])), 0, SUM([Value]) )