Difference between previously available month and the next available month

[trdoan]

Hi everyone,

I have this table:

Store Name	Order Quantity	Product Type	Posting Date
A	46	Husky	18/01/2018
A	49	Golden Retriever	18/04/2018
D	57	Shiba	18/09/2018
F	64	Beagle	18/12/2018
A	23	British Bulldog	18/1/2018
A	29	Bull Terrier	18/05/2018
C	46	Poodle	18/05/2018
F	52	Poodle	18/06/2018
D	29	Husky	18/09/2018

My Posting Date column has several discontinuous months. Can you please show me how to calculate the difference between Average Order Quantity from the previously available month and the next available month?

Ex:

AVG Order Quantity of A in January = (23 + 46) / 2 = 34.5

AVG Order Quantity of A in April = (23 + 46) / 2 = 49/1 = 49

Difference in AVG Order Quantity = 34.5 - 49 = - 14.5 units (the next available month from January for A is April)

Thank you so much!

[PattemManohar · Accepted solution]

@trdoan Please try the below steps

1. Create a new column in the source table

PostingDateYYYMM = FORMAT([PostingDate],"YYYYMM")

2a. Create a new table 

Test247Grp = SUMMARIZE(Test247CurrPrevMonth,Test247CurrPrevMonth[StoreName],Test247CurrPrevMonth[PostingDateYYYMM],"Avg",AVERAGE(Test247CurrPrevMonth[OrderQuantity]))

2b. Create a new columns as below

Rnk = RANKX(FILTER(Test247Grp,Test247Grp[StoreName]=EARLIER(Test247Grp[StoreName])),Test247Grp[PostingDateYYYMM],,ASC)

DiffAvg = 
VAR _CurrAvg = Test247Grp[Avg]
VAR _PrevAvg = LOOKUPVALUE(Test247Grp[Avg],Test247Grp[StoreName],Test247Grp[StoreName],Test247Grp[Rnk],Test247Grp[Rnk]-1)
RETURN IF(ISBLANK(_PrevAvg),0,_CurrAvg-_PrevAvg)