Moving quarterly average for the last 4 quarters

[Anonymous]

Hi, 

I have a simple table with 2 columns where I have month data and value for that month which updates every month (pic1). Based on this data I want to calculate the values for the last 4 complete quarters and 4 quarters before the last 4 quarters in two different tables. The quarterly value should be the average of the months in corresponding quarter. Since the last complete quarter is 2020 Q1, then the tables will look like this: 

The last 4 complete quarters:

Last 4 complete quarters

4 quarters before the last 4 quarters:

4 quarters before the last quarters

Pic 1

Can I please get help with this?

Thanks in advance!

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft,

Thanks for the reply. Unfortunately, I cannot open the file. It gives the following error: Object reference not set to an instance of an object.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

It may be caused by different client versions. Please do like this.

1. Create two calculated columns.

Quarter = [Period].[Year] & " " & [Period].[Quarter]

_Rankx = 
RANKX(
    Sheet3,
    [Quarter],
    , ASC,Dense
)

2. Create measures.

_Average = 
CALCULATE(
    AVERAGE(Sheet3[Indicator]),
    ALLEXCEPT( Sheet3, Sheet3[Quarter] )
)

_Filter1 = 
VAR x = 
CALCULATE(
    COUNT(Sheet3[Quarter]),
    ALLEXCEPT( Sheet3, Sheet3[Quarter] )
)
RETURN
IF(
    x = 3,
    1,
    0
)

_Filter2 = 
VAR x = 
CALCULATE(
    MAX( Sheet3[_Rankx] ),
    ALL(Sheet3)
)
RETURN
IF(
    SELECTEDVALUE(Sheet3[_Rankx]) <= x && SELECTEDVALUE(Sheet3[_Rankx]) >= x - 4,
    1,0
)

_Filter3 = 
VAR x = 
CALCULATE(
    MAX( Sheet3[_Rankx] ),
    ALL(Sheet3)
)
RETURN
IF(
    SELECTEDVALUE(Sheet3[_Rankx]) <= x-5 && SELECTEDVALUE(Sheet3[_Rankx]) >= x -9 ,
    1,0
) 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft,

This calculation works perfect if x=3 in _filter1 measure and when 1 is selected from the filter. But the calculation will not work if the quarter is incomplete. I want it to calculate last complete 4 quarter. Now I have added July 2020 as the latest month as an example to the model since it is beginnig of 3rd quarter. The table visual shows 2019 Qtr4, 2020 Qtr1, ans 2020 Qtr2 only. It should show the last 4 complete quarter. So, 2019 Qtr3, 2019 Qtr4, 2020 Qtr1, ans 2020 Qtr2.

Regards,

Asif