Quick measure rolling average shows future dates

[Anonymous]

How can I do to disappear with the months highlighted as shown below?

I don't need the future dates (oct to dec 2020).

My DAX for the one measure used in this axis:

Count of events 6 MO =

IF(

    ISFILTERED('tb_aog'[INICIO]),

    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 = ENDOFMONTH('tb_aog'[INICIO].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'tb_aog'[INICIO].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -5, MONTH)),

            ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))

        )

    RETURN

IF (

YEAR ( __LAST_DATE ) IN ALLSELECTED ( tb_aog[INICIO].[Year] ),

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('tb_aog'),

                    'tb_aog'[INICIO].[Year],

                    'tb_aog'[INICIO].[QuarterNo],

                    'tb_aog'[INICIO].[Quarter],

                    'tb_aog'[INICIO].[MonthNo],

                    'tb_aog'[INICIO].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE([Count of events], ALL('tb_aog'[INICIO].[Day]))

        )

))

Thanks in advance.

Marcos

[Greg_Deckler · Accepted solution]

@Anonymous - Use MIN instead of MAX

[Anonymous]

Great!! Thank you @amitchandak and @Greg_Deckler for your attention on this matter. Super helpful!!

Have you guys a great day!

[Anonymous]

Great!! Saved my day!! Fixed

[amitchandak · Accepted solution]

@Anonymous , another way out is

if( max('tb_aog'[INICIO].[Date]) >= Eomonth(today()-1) , blank(),

[Anonymous]

Perfect, but Sep 2020 disappeared.

[Greg_Deckler · Accepted solution]

@Anonymous - Use MIN instead of MAX

[amitchandak]

@Anonymous , Assuming 'tb_aog'[INICIO].[Date] is used on axis. else use the date on axis

Try

Count of events 6 MO =
if( max('tb_aog'[INICIO].[Date]) >= today(), blank(),
IF(
ISFILTERED('tb_aog'[INICIO]),
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 = ENDOFMONTH('tb_aog'[INICIO].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'tb_aog'[INICIO].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -5, MONTH)),
ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
)
RETURN
IF (
YEAR ( __LAST_DATE ) IN ALLSELECTED ( tb_aog[INICIO].[Year] ),
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('tb_aog'),
'tb_aog'[INICIO].[Year],
'tb_aog'[INICIO].[QuarterNo],
'tb_aog'[INICIO].[Quarter],
'tb_aog'[INICIO].[MonthNo],
'tb_aog'[INICIO].[Month]
),
__DATE_PERIOD
),
CALCULATE([Count of events], ALL('tb_aog'[INICIO].[Day]))
)
)) )