Forum Discussion

Bilin_00's avatar
Bilin_00
New Member
2 years ago

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...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Consider using WINDOW instead.

 

What is your expected result?

  • Bilin_00's avatar
    Bilin_00
    New Member
    2 years ago

     

    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's avatar
      lbendlin
      Super User
      2 years ago
      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)
      • Bilin_00's avatar
        Bilin_00
        New Member
        2 years ago

        It's displaying same output as before