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. Even the blank dates like 6/14 should calculate the last 5 non-blank values.

 

Any help is always appreciated!

 

2 Replies

  • 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!