Struggling with rolling average (bizarre)

[RolandoVG]

Hi, I am struggling with this, I'm practicing Power BI, have two tables, Calendar and Sales, Calendar table extends dates till december 2011, while Sales tables dates only go to december 2009.

When I try to build a rolling average 3 months, results extends to february/2010, which is incorrect.

I know, it could be solved just filtering or removing non existent dates from Calendar table, but it is a challenge on how to control this situation, maybe it is unprobable in real life, but I'd like to know if it is possible workaround this without delete or filter anything.

This is what I did for rolling average

Movil3MesesListo =

CALCULATE(

         SUM(FactSales[Cantidad Total]),

         DATESINPERIOD(DimCalendar[DateKey],LASTDATE(DimCalendar[DateKey]),-3,MONTH)

)/ CALCULATE(

         DISTINCTCOUNT(DimCalendar[Mes]),

         DATESINPERIOD(DimCalendar[DateKey],LASTDATE(DimCalendar[DateKey]),-3,MONTH)

)

Many thanks in advance

[TomMartens · Accepted solution]

Hey @RolandoVG ,

CALCULATE(...) has to used whenever an existing filter context has to be modified. As you are going to calculate the rolling average for the last three months, you have to use CALCULATE.

The 1st parameter is the easy part, it's the numeric expression, here SUM(...), the 2nd to nth parameter are the filter modifier. Here you are using DATESINPERIOD(...).

What happens in January is this. DATESINPERIOD returns a table that contains the dates for 1st, of November 2009 to 31, of January 2010. This means the SUM is created adding (1253... + 1301... + null) / 3.

The same happens in February 2010 
This means the SUM is created adding (1301... + null + null) / 3.

I say this is correct, this is how DAX works, but maybe this is not what you are expecting 😉

If you want to avoid the calculation if SUM('...'[Cantidad Total]) is null then you have to wrap the complete calculation into an if statement like so:

measure = 
IF(NOT(ISBLANK(SUM('...'[Cantidat Total]))
, CALCULATE(...)
, BLANK()
)

Hopefully, this is what you are looking for.

Regards,

Tom

[RolandoVG]

Many thanks @TomMartens , that worked very well.