Max value of a SMA (Simple Mobile Average) MEASURE in selected range

[Francisco_G_Cal]

Hi

I have a measure, an SMA (Simple Mobile Average), and I need to calculate the MAX of it.

It should be simple, but I can't find what am I doing wrong.

In my case I have a table (allDataCSV). Each row represents an event, and it has a DATE. I can calculate the num of events that occur in when filtering the table (just count rows):

Count = COUNTROWS()

And my SMA is calculated this way:

CountSMA5Days = AVERAGEX(

        DATESINPERIOD(allDataCSV[Day],LASTDATE(allDataCSV[Day]),-5,DAY),

            [Count])

I want to calulate the MAX value of [CountSMA5Days]. It is a measure, not a field, so I cant use MAX. I have tried MAXX, with no luck:

MaxSMA = MAXX(allDataCSV,[CountSMA5Days])

It returns me '1', and I understand it is because it evals row by row, and, in ths case, [CountSMA5Days] returns an average of 5 'ones'

How can I get the MAX of [CountSMA5Days], that, in that case, should be 213?

[Francisco_G_Cal · Accepted solution]

I finally found the solution. Its a small modification to my original measure

MaxSMA = MAXX( all(allDataCSV[Day]), [CountSMA5Days])

I came to this solution thanks to another PowerBI user, who guided me with this comment:

I mean your measure MaxSMA = MAXX(allDataCSV,[CountSMA5Days]) DAX converts to MaxSMA = MAXX(allDataCSV,CALCULATE([CountSMA5Days])). So, all data in rows filters your measure.

Thats why count is ALLWAYS 1 when calculating [CountSMA5Days], and so, MAXX is 1. So, the solution is remove filter only for Dates, so I get all dates, but rest of filters apply and [Count] is calculated well.

https://docs.microsoft.com/en-us/dax/all-function-dax

ALL (Column[, Column[, …]])
Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.

That's it!

[DataInsights]

@Francisco_G_Cal,

Try this measure:

MaxSMA =
CALCULATE ( [CountSMA5Days], ALLSELECTED ( allDataCSV ) )

[Francisco_G_Cal]

Sorry, Nope.

It gives me a value of 73,6, that I think could be the average of all [Count]. The 'ALLSELECTED ( allDataCSV )' removes all context for 'allDataCSV', so the AVERAGEX it does not takes only last five days, but all days.

Thaks for your post, witch helped me for another need I had 🙂