How to exclude future dates on rolling average dates

[Bilin_00]

I am trying to exclude rolling average for the future days. Here is my formula, I tired all possible ways to exclude the future dates but nothing is working. Please give me some suggestions how to do this. I have attached screen clip of the table and my formula. Waiting for some suggestions and solution. Thank you!!

 In this priced column is count of each date from the orginal table. For instance, 12 on 24th April 2024 means 12 items has been priced.

Rolling Average 2 =

IF(

    ISFILTERED('Query2'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('Query2'[Date].[Date])

    VAR __TODAY = TODAY()

    VAR __Calc =

        AVERAGEX(

            FILTER(

                DATESBETWEEN(

                    'Query2'[Date].[Date],

                    DATEADD(__LAST_DATE, -30, DAY),

                    DATEADD(__LAST_DATE, 0, DAY)

                ),

                'Query2'[Date].[Date] <= __TODAY

            ),

            CALCULATE(COUNTA('Query2'[Date]))

        )

    RETURN

        IF(MAX('Query2'[Date]) > __TODAY, BLANK(), __Calc)

[lbendlin]

Consider using WINDOW instead.

What is your expected result?

[Bilin_00]

 

I don't want rolling average line(black) after the red mark which is today date. I need last 30 days rolling average till today's date.

[lbendlin]

Rolling Average 2 =

IF(

    ISFILTERED('Query2'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('Query2'[Date].[Date])

    VAR __TODAY = TODAY()

    VAR __Calc =

        AVERAGEX(

            FILTER(

                DATESBETWEEN(

                    'Query2'[Date].[Date],

                    DATEADD(__LAST_DATE, -30, DAY),

                    DATEADD(__LAST_DATE, 0, DAY)

                ),

                'Query2'[Date].[Date] <= __TODAY

            ),

            CALCULATE(COUNTA('Query2'[Date]))

        )

    RETURN

        IF(MAX('Query2'[Date]) > __LAST_DATE, BLANK(), __Calc)

[Bilin_00]

It's displaying same output as before

[lbendlin]