How to exclude future dates on rolling average dates

Question

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.&nbsp;Rolling Average 2 = IF(&nbsp; &nbsp; ISFILTERED('Query2'[Date]),&nbsp; &nbsp; ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),&nbsp; &nbsp; VAR __LAST_DATE = LASTDATE('Query2'[Date].[Date])&nbsp; &nbsp; VAR __TODAY = TODAY()&nbsp; &nbsp; VAR __Calc = &nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Query2'[Date].[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEADD(__LAST_DATE, -30, DAY),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEADD(__LAST_DATE, 0, DAY)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Query2'[Date].[Date] &lt;= __TODAY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(COUNTA('Query2'[Date]))&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; IF(MAX('Query2'[Date]) &gt; __TODAY, BLANK(), __Calc)

lbendlin · Answer

Consider using WINDOW instead.
&nbsp;
What is your expected result?

bilin_00 · Answer

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 · Answer

Rolling Average&nbsp;2&nbsp;=
IF(
&nbsp; &nbsp;&nbsp;ISFILTERED('Query2'[Date]),
&nbsp; &nbsp;&nbsp;ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
&nbsp; &nbsp;&nbsp;VAR&nbsp;__LAST_DATE =&nbsp;LASTDATE('Query2'[Date].[Date])
&nbsp; &nbsp;&nbsp;VAR&nbsp;__TODAY =&nbsp;TODAY()
&nbsp; &nbsp;&nbsp;VAR&nbsp;__Calc =
&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;AVERAGEX(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FILTER(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DATESBETWEEN(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Query2'[Date].[Date],
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DATEADD(__LAST_DATE, -30, DAY),
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DATEADD(__LAST_DATE,&nbsp;0, DAY)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Query2'[Date].[Date] &lt;= __TODAY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATE(COUNTA('Query2'[Date]))
&nbsp; &nbsp; &nbsp; &nbsp; )
&nbsp; &nbsp;&nbsp;RETURN
&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF(MAX('Query2'[Date]) &gt; __LAST_DATE,&nbsp;BLANK(), __Calc)

bilin_00 · Answer

It's displaying same output as before

lbendlin · Answer

&nbsp;

Forum Discussion

How to exclude future dates on rolling average dates

5 Replies

Related content

Rolling 12M Average measure

Exclude blank weeks from Rolling Average

Average excluding zero's

3 Day Rolling Average - DAX

Rolling average exclude future dates