Find MAX 7 day average with DAX

Question

I am stuck trying to build a measure to determine the best or max 7 day average over a period longer than 7 days.

I have multiple values for each day, the measure needs to sum the values by day then calculate a rolling 7 day average then determine which of those results is the MAX value. Could you help point me in the right direction please?

Seymour Seymour · Accepted Answer · 2020-06-08T19:59:53

You need to use define a column in your measure and run an iterator (MAXX) over it in order to find the maximum value. Without additional details about fields and data model it is hard to be more specific.

However, see below an example:

VAR _tbl = CALCULATETABLE(ADDCOLUMNS(
                      SUMMARIZE('Fact', 'Dimension 1'[Attribute 1], 'Dimension 2'[Attribute2]),
                      "@average", AVERAGE('Fact'[Attribute 2])
           ), ALLSELECTED('Dimension 1'), ALLSELECTED('Dimension 2))
RETURN
MAXX(_tbl, [@rolling_average])

In practice, first we define a table with the measures pre-calculated, then, we run an iterator on that table in order to retrieve the maximum value.

Find MAX 7 day average with DAX

1 Answers