Problem with a POWER BI rolling formula (DAX)

[EM2]

Hi everyone 🙂
I have a table in PowerBI that has specific values for each month (Actual) and I have to create a measure that aggregates the values of the next 3 months rolling. But if any of the 3 values is blank, the formula should return a blank value. (so the blue value should be blank).

SKU	Month	Actual (qty)	To be clean	Aggregate 3 months rolling
71181	November	3.423		8.166
71181	Dicember	765	1
71181	January	3.978

I have created these 2 formulas, but they are not working:

Actual Cleaned =
VAR MaxDate= MAX(Lut_Calendar[Data Calendar])
return
CALCULATE(
SUM(Drop_Forecast[Actual]),FILTER(Drop_Forecast,Drop_Forecast[to be clean]<>1)
)

Actual 3M cleaned =
VAR MaxDate= MAX(Lut_Calendar[Data Calendar])
return
IF([Actual Cleaned]<>BLANK(),
CALCULATE(
SUMX('Drop_Forecast 3M',[Actual Cleaned])
,
FILTER(
ALL(Lut_Calendar[Data Calendar]),
AND(
Lut_Calendar[Data Calendar]<=MaxDate,
DATEADD(Lut_Calendar[Data Calendar],3,MONTH)>MaxDate
)
)
),BLANK())

Thanks for your help! 🙂

[Anonymous]

Hi @EM2 ,

How is 8.166 calculated? The sum of 3.423,765 and 3.978 is not 8.166.

And also, you may have a date column in your table, right?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.