Forum Discussion

MrMarshall's avatar
MrMarshall
Icon for Helper II rankHelper II
7 years ago
Solved

Quick measure rolling average shows future dates

Today, I have a separate Date table and a Sales table. This time I was creating a rolling average with a quick measure, using the Sales amount field in the sales table and the primary Date field on ...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    MrMarshall,

     

    You may try adding IF.

        RETURN
        IF (
            YEAR ( __LAST_DATE ) IN ALLSELECTED ( 'Calendar'[Year] ),
MrMarshall's avatar
MrMarshall
Icon for Helper II rankHelper II
7 years ago

Thanks!

Really saved my day, and week, and year!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How can I do that in my case 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