cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PNovak Frequent Visitor
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
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.

View solution in original post

3 REPLIES 3
Super User III
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.

View solution in original post

PNovak Frequent Visitor
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
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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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

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