Exclude values with date grater than today in Averagex measure

[ShabibAH]

Please help me in this. I am using this measure to calculate monthly average from beginning of the year.

For the current month Ex. Jun-2023 I want the measure to consider Sales values till today and get average. (exclude the Sales where Date > today)

AvgM Sales = CALCULATE(
    AVERAGEX(
        VALUES(MainTable[Date]),
        CALCULATE(SUM(
            MainTable[Sales]))),
            DATESMTD(ENDOFMONTH(MainTable[Date])))

[ShabibAH]

Thank you @Jihwan_Kim , this works exactly ..

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

Avg Sales: =
VAR _endofmonth =
    ENDOFMONTH ( MainTable[Date] )
VAR _t =
    FILTER (
        MainTable,
        ENDOFMONTH ( MainTable[Date] ) = _endofmonth
            && MainTable[Date] <= TODAY ()
    )
RETURN
    AVERAGEX ( _t, MainTable[Sales] )