Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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.

 

duncanh_0-1624048383370.png

Any help is always appreciated!

 

2 REPLIES 2
AlB
Community Champion
Community Champion

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] )

 

SU18_powerbi_badge

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
Not applicable

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.