Add date filter to 7 day moving average formula

[monojchakrab]

Hiya All,

I have a 7 day moving average code as shown below :

7 day MA = 

CALCULATE(
    AVERAGEX(SUMMARIZE('Date Table', 'Date Table'[Date]),
          'Measures tray'[Total Sales]),
              DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]) ,-7,DAY)
)

But the date table is of course auto-populating to the current date - which is 5th of may. But the fact table currently has data updated only till 29th of Apr.

Is it possible to suitably modify this code, to include a date filter to filter for only date <=29/04/2022?

[I know this can be done thru' the filter on the page but I wanted to solve it thru' the code itself]

Thanks and really appreciate your time

Best

[PC2790 · Accepted solution]

Did you try using the date from your Fact table instead of Dates Table?

As in LASTDATE(DatefromFactTable)

[monojchakrab · Accepted solution]

That works of course - but I wanted to avoid that, since I have generated a date table separately and wanted to work off that, with filters to filter to the dates I want to keep in the visual

[PC2790 · Accepted solution]

Did you try using the date from your Fact table instead of Dates Table?

As in LASTDATE(DatefromFactTable)