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

user_2587931 0
Viewed 33

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 suppose it is because it evals row by row, and, in ths case, [CountSMA5Days] returns an average of 5 'ones'

Graph and table with 'count' through time and 'count SMA in 5 days'

Could you help me, please?

Thanks!

EDIT:

Thans to @Mik, who guided me to solution. The right meassure is:

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

That's it!!!

dax powerbi
1 Answers

The problem with your measure can come from CALCULATE() that you get by enclosed measure.

I mean your measure MaxSMA = MAXX(allDataCSV,[CountSMA5Days]) DAX converts to MaxSMA = MAXX(allDataCSV,CALCULATE([CountSMA5Days])). So, all data in rows filters your measure. Try the same trick as you did with AVERAGEX()

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

I believe that DAX will convert it to

MAXX(
    DATESINPERIOD(allDataCSV[Day],LASTDATE(allDataCSV[Day]),-5,DAY)
    ,CALCULATE(
          AVERAGEX( -- your measure
               DATESINPERIOD(
                   allDataCSV[Day]
                   ,LASTDATE(allDataCSV[Day]) --= date from current iteration of MAXX
                   ,-5
                   ,DAY
                )
                ,CALCULATE([Count])
          )
          ,allDataCSV[Day]= date from current iteration 
     )
)

I didn't check the measure. Hope you'll manage to fix the issue.

user_190423790
Related