cancel
Showing results for
Did you mean:
Highlighted
PNovak Frequent Visitor

## Sum of rolling measures

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!

1 ACCEPTED SOLUTION

Accepted Solutions Super User III

## Re: Sum of rolling measures

Hi @PNovak,

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.

3 REPLIES 3 Super User III

## Re: Sum of rolling measures

Hi @PNovak,

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.

PNovak Frequent Visitor

## Re: Sum of rolling measures

@sturlaws , your first suggestion worked like a charm!

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

Thanks! Super User III

## Re: Sum of rolling measures

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.

Announcements #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January! Top Solution Authors
Top Kudoed Authors
Users online (2,050)