Average of the last 10 values excluding the blank values on random dates inbetween

[Anonymous]

So im really having a hard time with making an average of the last 10 values excluding the blanks. the average must take into consideration that the blanks inbetween the values are inconsistent. I hope you get the picture.  I used the command below but it only copied the values instead of averaging them:

Average_Last_10_CZV_Values =

VAR AllDates_CZV =

ADDCOLUMNS (

VALUES ( 'afvalwater CPL'[Datum] ),

"@CZV", CALCULATE ( SUM ( 'afvalwater CPL'[CZV mg/l] ) )

) // get rid of any dates that don't have a element type amount

VAR Remove_BlankCZV =

FILTER ( AllDates_CZV, [@CZV] <> BLANK () ) // get the 10 most recent dates

VAR MostRecentDates =

TOPN ( 10, Remove_BlankCZV, 'afvalwater CPL'[Datum], DESC ) // add'em up

VAR Result =

AVERAGEX ( MostRecentDates, [@CZV] )

RETURN

Result

Before, I used to edit the formula bar each week in Excel to update the values so it would only average the last 10 values like this:

If anyone could help out that would be great!

thank you

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like the attached file.

Last 10 CZV avg measure: =
VAR newtable =
    TOPN (
        10,
        FILTER ( ALL ( Data ), NOT ISBLANK ( Data[CZV] ) ),
        Data[Date], DESC
    )
RETURN
    IF (
        SELECTEDVALUE ( Data[Date] ) >= MINX ( newtable, Data[Date] )
            && SELECTEDVALUE ( Data[Date] ) <= MAXX ( newtable, Data[Date] )
            && NOT ISBLANK ( [CZV total measure:] ),
        AVERAGEX ( newtable, Data[CZV] )
    )

[Anonymous]

Thanks a bunch! this worked for me.

[johnt75]

Try replacing VALUES ( 'afvalwater CPL'[Datum] ) with ALLSELECTED ( 'afvalwater CPL'[Datum] )

[Anonymous]

This didnt work. gave me duplicate values instead. thank you tho!