Last 12 Mo. Average $ Sales Measure to Include Months with Zero Dollars

[Christina_C]

Below is a matrix of sales for the last 13 months.  I am looking to calculate the last 12 month average. Within my measure "Avg $ Sales/ 12 Mo", it is not including the months with zero dollars in sales.  The result I am looking for should be 236, not 707. 

Can anyone help me update this measure?  Please and thank you 🙂 

Average Last 12 Mo. =

VAR NumOfMonths = 12

VAR LastSelectedDate = MAX ( 'Calendar'[Date] )

VAR Period =

    DATESINPERIOD ( 'Calendar'[Date], LastSelectedDate, -NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        AVERAGEX (

            VALUES ( 'Calendar'[Month Number] ),

            [Total Sales]

        ),

        Period

    )

VAR LastDateWithSale = MAX ( 'VALUE ENTRY'[Posting Date] )

VAR FirstVisibleDate = MIN ('Calendar'[Date])

RETURN

    IF( FirstVisibleDate <= LastDateWithSale, Result )

[Christina_C]

@amitchandak It worked!! Thank you so much!!

[amitchandak · Accepted solution]

@Christina_C , if you want inculde month with no sales

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))/12

Do not inculde month with 0 sales

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))