Calculate last 7 days average using the last non-blank value

[Anonymous]

Hello,

I have a measure that calculates the last 7 days average starting from today and ranging back one week. The problem is that usually our data is about 2 weeks behind so I am looking for a way to modify the measure to find the last non blank value and then subtract the last 7 days. Please ignore last part, it just filters the type. THANKS!

7DayAvg =

CALCULATE(

    AVERAGE(LabResults[Result]),

    (FILTER(DimDates, DimDates[Date] < today()  &&  DimDates[Date] >= today() - 7)),

    CONTAINSSTRING(LabResults[Type], "Test A"))

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please try this measure. I assume that for dates that don't have values, LabResults table will have blank in Result column. 

7DayAvg =
VAR _lastDate = MAXX ( FILTER ( LabResults, LabResults[Result] <> BLANK () ), LabResults[Date] )
RETURN
    CALCULATE (
        AVERAGE ( LabResults[Result] ),
        (
            FILTER (
                DimDates,
                DimDates[Date] <= _lastDate
                    && DimDates[Date] > _lastDate - 7
            )
        ),
        CONTAINSSTRING ( LabResults[Type], "Test A" )
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.