cancel
Showing results for
Did you mean:
Highlighted
Member

## Difference between previously available month and the next available month

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Difference between previously available month and the next available month

@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)```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Super User

## Re: Difference between previously available month and the next available month

@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)```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !