Help with non-blank moving average

[duncanh]

I need like an advanced version of a 5-day moving average. Per my example below, I'd like 6/18 to show the average of the last 5 non-blank values. Then the same thing for every other date as well. Even the blank dates like 6/14 should calculate the last 5 non-blank values.

Any help is always appreciated!

[AlB]

Hi @duncanh 

Try this measure. See it all at work in the attached file.

Measure = 
VAR currentDate_ =
    SELECTEDVALUE ( Table1[Date] )
VAR latestNonBlank5_ =
    TOPN (
        5,
        FILTER (
            ALL ( Table1[Date], Table1[Sales] ),
            Table1[Date] <= currentDate_
                && NOT ISBLANK ( Table1[Sales] )
        ),
        Table1[Date], DESC
    )
RETURN
    AVERAGEX ( latestNonBlank5_, Table1[Sales] )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[duncanh]

Hi @AlB ,

This solution works for a singular table, but not for my cube. I have to make some modifications to the cube and this should work. Your answer is correct and I will accept this as a solution, but will keep it open in case others have ideas or until I get this to work on my cube.

Thanks!