Percent Change of Previous 3 month period compared to current month

[ahammond01]

Hi, 

I would like to be able to calculate the percentage change of the car registrations during the previous 3 months compared to the current month's data. 

I am very new to DAX, so I will just show you an example of how I used to calculate this when doing it in excel. My sample data is below as well.

Percentage change= (Current Month-Prior 3 Month Average)/ (Prior 3 Month Average)

EX: Percentage change for Brazil= (157-average(147,136,189))/average(147,136,189).

each month I will be adding the latest month's data to this list, so it would be great if this could auto- adjust to the previous 3 months vs the current month values. 

Country	Date Added	Car Registrations
Brazil	2/17/2017	147
Brazil	3/17/2017	136
Brazil	4/17/2017	189
Brazil	5/17/2017	157
Canada	2/17/2017	128
Canada	3/17/2017	113
Canada	4/17/2017	125
Canada	5/17/2017	191
China	2/17/2017	2,218
China	3/17/2017	1,633
China	4/17/2017	2,096
China	5/17/2017	1,722
Euro Area	2/17/2017	895
Euro Area	3/17/2017	900
Euro Area	4/17/2017	908
Euro Area	5/17/2017	904
India	2/17/2017	244
India	3/17/2017	240
India	4/17/2017	263
India	5/17/2017	254
Indonesia	2/17/2017	100
Indonesia	3/17/2017	87
Indonesia	4/17/2017	95
Indonesia	5/17/2017	101

Does anyone have any ideas for how to do this?

[Vvelarde · Accepted solution]

@ahammond01

Hi let's go to find a solution:

Create these measures

CurrentMonth =
VAR CMonth =
    MONTH ( LASTDATE ( Table1[Date Added] ) )
RETURN
    CALCULATE (
        SUM ( Table1[Car Registrations] ),
        FILTER ( Table1, MONTH ( Table1[Date Added] ) = CMonth )
    )

Average3MonthsPrev =
CALCULATE (
    AVERAGE ( Table1[Car Registrations] ),
    DATESBETWEEN (
        Table1[Date Added],
        STARTOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -4, MONTH ) ),
        ENDOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -1, MONTH ) )
    )
)

PercentageChange =
DIVIDE ( [CurrentMonth] - [Average3MonthsPrev], [Average3MonthsPrev], 0 )

Let me know if works in your scenario.

[ahammond01]

Is there a way to update the Current Month and Average measure so that it picks up the last date for each country? For example, sometimes a few countries have not made an update to their data in a few months so we may be in June for other months but their data only goes up to February. In that case the formula does not work. 

Here's an example: 

Country	Date Added	Car Registrations
Thailand	1/17/2017	32
Thailand	2/17/2017	35
Thailand	3/17/2017	41
Thailand	4/17/2017	27
United States	11/17/2016	488
United States	12/17/2016	554
United States	1/17/2017	441
United States	2/17/2017	502

For the US... here is what I get using the measures in the previous message: 

  But it should be 502 current month and 494.33 for the average so a 1.5%  change. (502-494.33)/494.33

[ahammond01]

This worked Perfectly!!! Thank you so much.