Issue with 12 Month Moving Average Calculation - Need Help

[dmalviya]

Hi,

i am facing issue while calculating 12 month moving average.  I am using below formulas -

Number of periods =
CALCULATE (
    CALCULATE ( COUNTROWS ( VALUES ( DimDate[MonthName] ) )),
    DATESBETWEEN (
        DimDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Date] ) ) ),
        LASTDATE ( DimDate[Date] )
    )
)

--------------------------------------------------------------------------------------

12 Month Sales Moving Average =
CALCULATE (
    [TotalVolume],
    DATESBETWEEN (
        DimDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Date].[Date] ) ) ),
        LASTDATE ( DimDate[Date].[Date] )
    )
)
    / [Number of periods]

-------------------------------------------

the above formula is working correctly. But there is one issue. Sales table is missing some months transaction. Like there is no transaction for October, November & December 2018 month but still graph shows [12 Month Moving Average] for these months.

Similarly few months are missing from January to September also but value is coming.

My requirement is that graph should show [12 Month Moving Average] for the months for which transactions available.

DimDate is monthly & generated for full 2017 & 2018 year. All months are covered.

Sales table has sales data but some months are missing.

Now you see on above picture some value is coming as Blank & also value for October, November & Deceber 2018. But there is no transaction for these months.

Please suggest how to fix this issue.

[v-cherch-msft]

Hi @dmalviya

You may try to remove .[Date] in the formula. Below is the article for your reference. If it is not your case, please share some data sample. You can upload it to OneDrive or Dropbox and post the link here.

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Regards,

Cherie