Forum Discussion

Helper 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 ...

7 years ago

You may try adding IF.

    RETURN
        IF (
            YEAR ( __LAST_DATE ) IN ALLSELECTED ( 'Calendar'[Year] ),

Community Support

7 years ago

You may try adding IF.

    RETURN
        IF (
            YEAR ( __LAST_DATE ) IN ALLSELECTED ( 'Calendar'[Year] ),

MrMarshall
Helper II
7 years ago
Thanks!

Really saved my day, and week, and year!
- Anonymous
  Not applicable
  5 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