3 Month Moving Average that Ignores Blank Values

[pulpfiction]

Hello! 

I need to create a table that shows a 3 month moving average that ignores Blank values. So far I have been able to create the following standard moving average measure: 

OrderLine Value 3M MAV =

VAR DateFilter =

DATESINPERIOD(

'Calendar'[DATE],

MAX('Calendar'[DATE]),

-3, MONTH)

VAR OrderLineMA3 =

CALCULATE(

[OrderLine Value],

DateFilter)

RETURN

DIVIDE(OrderLineMA3, 3, 0)

However, it doesn't return the value I need. This is what I have:
  

I need the measure to ignore previous months that have Blank Values in the "Value" field. ("Value" is also a measure.)
For example, the value for the 3 Month MAV for July 2020 should be an average of the months of July, June and February and the result should be (895 + 228 + 29) / 3 = 384 and not 86. 

[v-shex-msft]

Hi @pulpfiction,

Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @pulpfiction,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

See if my solution here helps - Calculate rolling sum for the past week by ignoring blank cells.

[pulpfiction]

Hey Ashish and thank you for the solution provided! Unfortunately, I couldn't make it work for my case as I have missing dates in my dataset - there are days in my data without an orderline, therefore this day is missing. 

[vanessafvg]

this looks good @Ashish_Mathur   i thought topn might be the solution and seems it is, thanks for sharing.

[Ashish_Mathur]

If my previous reply helped, please mark it as Answer.

[rsbin]

@pulpfiction,

I understand what you are trying to do.  Don't know of an exact solution, but you have to do away with your current DatesInPeriod logic.  That only works assuming you have Values in the previous 3 months.  If one of those months is 0, then you have to go back an additional month. 

Thinking out loud, I think a virtual table where you find the last 3 non-zero Values.  Then sum these up and divide by 3.  I am by no means an expert in the syntax for this, but I hope this gives you another approach to this problem.

Hope this helps.

[pulpfiction]

Hey rsbin, I will try your suggestion for a virtual table and will let you know if it worked. Thank you! 

[vanessafvg]

agree, you probably need to create a table with the last 3 values that are not 0 and you will not be able to use a date function because the last 3 periods will always be the last 3 months ) 0 or >0.

however if you provide data in text format, it will mean that someone can use the data to work through the issue.  so post the data in text format.