cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

Difference between previously available month and the next available month

Hi everyone,

 

I have this table:

 

Store NameOrder QuantityProduct TypePosting Date
A46Husky18/01/2018
A49Golden Retriever18/04/2018
D57Shiba18/09/2018
F64Beagle18/12/2018
A23British Bulldog18/1/2018
A29Bull Terrier18/05/2018
C46Poodle18/05/2018
F52Poodle18/06/2018
D29Husky18/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
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)

image.png



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

Proud to be a Datanaut !





1 REPLY 1
Super User
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)

image.png



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

Proud to be a Datanaut !