Average for last 12 moving periods

[Manar]

Hi, 

I have the following data  (Sample):

Currency         Rate    ReportingPeriod    

AED                 0.1             201502

AFN                0.03            201506

Ak1                 0.04           201502

AUD                0.01           201603  

I am trying to get the average of Rates for the last 12 periods and group them by Currency. The issue I am having is the to get the 12 moving periods (Period is YearMonth) . 

Any help or clue on how to do this.

Thank you 

[afzalphatan]

I would recommend to user power query to modify the data in a well structured way...so that you can come back to it any time.. 

why dont you seperate year and month using power query , it would then be easy.

[Manar]

@afzalphatan  I am using Direct Mode so can't change datatype. 

[MFelix]

Hi @Manar,

Althoug I don't have complete information,

Please check this post where I believe it's a similar problem than yours the only difference is the time that is ni days instead of months.

If you need any additional information please share some data and expected result.

Regards,

MFelix

[Manar]

The outcome I want

Thank you @MFelix   The issue I am having is ReportingPeriod is a varchar, so I can't write an expression and use ReportingPeriod - 12 , because even if it subtracts the number I will get doesn't make sense, for example, if Reporting Period is 201803 and I want the average for the last 12 reporting period  201803-12 = 201791 which doesn't make sense it should be 201703

what would you recommend?  

Thank you 

[MFelix · Accepted solution]

Hi @Manar,

If you have a var char you only need to do it with the 99 in the example below my table as monthyear as you have and values are 100 per line as you can see until december 2016 Dec the values are all 1200 before that is reducing 100 per month since the data is only starting in january 2016.

The calculated measure I created was:

calculate =
VAR Selected_Month_Year =
    MAX ( Data[MonthYear] )
RETURN
    CALCULATE (
        SUM ( Data[Value] );
        Data[MonthYear]
            >= Selected_Month_Year - 99
            && Data[MonthYear] <= Selected_Month_Year
    )

Regards,

MFelix

[Manar]

@MFelix  Thank you!