Forum Discussion
How to exclude future dates on rolling average dates
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...
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.
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)