Sum of rolling measures

[Anonymous]

Hello people!

I'm still new to Power BI, so forgive me if I am making some kind of primary mistakes.

I have this measure, that gives me a rolling average for each month of the year:

TESTE = CALCULATE(AVERAGEX(VALUES(DateKey[Data]);'Base Ligações'[Total Ligações]);DATESINPERIOD(DateKey[Data];MAX(DateKey[Data]);-3;MONTH))

Without any filters, the measure shows up the average of the present month (which is composed by the sum of 'Base Ligações'[Total Ligações] from the current month and the last two months, then divided by 3). If a month filter is applied via a slicer, it shows correctly the average of that montht and it's two predecessors.

What I want now is to sum all of those averages,i.e., I want the total of all averages for a specifc year, keeping the correct results if a specific month is selected via a filter in a slicer.

I've tried SUM, SUMX on the TEST measure, but it simple doesn't work at all.

I appreciate any help!

[sturlaws · Accepted solution]

Hi @Anonymous,

have I understood you correct: when no month is selected, you want to sum the averages like this: average(dec, nov, oct) + average(nov,oct,sep) + average(oct,sep,aug) + ...... 

You state you want to see it for specific year, where should the series above end, at average(mar, feb, jan) or (jan,dec,nov)?

If I have understood you, and you want the series to end at (mar,feb,jan)+(feb,jan)+(jan) this will work(probably, I have just tested in on a very simple mockup model i created, without the option for timeintelligence functions, so the version of [teste] I created were not exactly like yours)

Measure =
IF (
    NOT ( HASONEVALUE ( test[month] ) );
    SUMX ( VALUES ( test[month] ); [TESTE] );
    [TESTE]
)

Now, if you want to have you series to end at (jan,dec,nov), it is a bit more complicated. I had to rewrite your measure to this:

TESTE =
VAR _maxMonth =
    CALCULATE ( MAX ( test[month] ) )
VAR _prevMonth =
    DATE ( YEAR ( _maxMonth ); MONTH ( _maxMonth ) - 2; 1 )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( test[salg] );
            FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
        );
        COUNTROWS (
            CALCULATETABLE (
                VALUES ( test[month] );
                FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
            )
        )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

@sturlaws , your first suggestion worked like a charm!

But I really doesn't understand how and why..can you explain it?

Thanks!

[sturlaws]

Sure thing.

When you choose e.g. year = 2019 in a slicer, the [month]-column contains all the months of 2019. So the HASONEVALUE-function returns FALSE. I have wrapped NOT() around it, so the condition in the IF-function evaluates to TRUE if the HASONEVALUE-function evaluates to false.

When the condition evaluates to TRUE, the SUMX-function is called. SUMX is an iterator-function. The first argument of SUMX is a table(or table expression). In this case, VALUES(test[Month]) returns a 1-column table with the distinct [months] in the current context. So if year = 2019 in a slicer, VALUES(test[Month]) returns the months of 2019. The second argument of SUMX() is the measure [TESTE]. This measure is evaluated once for every row in the table generated by VALUES([month]), with the corresponding month as context for the measure evaluation. When it is evaluated for all rows in VALUES([month]) the results are summed up and returned. 

There is one last thing to consider with this measure. If you use a slicer(or any other filter) to choose year=2019, that is part of the context for which the measure is evaluated. So for january only the january value is added to the average, because the dec-2018 and nov-2018 is not present in the context of 2019.

Now, if the is something filtering the [month]-column, e.g. a slicer or crossfiltering from another table, HASONEVALUE returns true. Then [TESTE] is just evaluated for just that month.