Can I filter a column relative to the max value in that column?

Question

I'm trying to create a measure that is the sum of the last 7 days with sales data.

The following code only works as desired when sales data is recent and there are no date filters that clash.

CALCULATE([Sold Dollars], 
          'calendar'[Date] <= TODAY()-1) && 'calendar'[Date] >= TODAY()-7)

I tried this code:

CALCULATE([Sold Dollars], 
          'calendar'[Date] <= MAX('calendar'[Date])-1
          && 'calendar'[Date] >= MAX('calendar'[Date])-7)

But it returns an error message:

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

What can I do to create a measure that is filtered to the top seven ALLSELECTED dates?

RADO RADO · Accepted Answer · 2019-05-15T22:58:31

There are several ways to do it.

To avoid the error message you received, you can save max date in a variable, and then use it as a filter condition in CALCULATE.

Or, you can use this approach:

Seven Day Sales =
VAR 
  Latest_Date = MAX ( 'calendar'[Date] )
RETURN
    CALCULATE (
        [Sold Dollars],
        DATESINPERIOD ( 'calendar'[Date], Latest_Date, -7, DAY )
    )

You can adjust the calculation of the "Latest_Date" variable to your needs.

Can I filter a column relative to the max value in that column?

1 Answers