Struggling with rolling average (bizarre)

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