Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help with non-blank moving average

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. Ev...
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous 

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!