New column to show shipping status query

[Deeks]

Hi, 
I am trying to create a new column which would indicate when a Part Number has appeared in my dataset for the very first time, for it/them to labelled as "New", 

In the same column I want it to also indicate if the same Part Number is appearing in multiple weeks post it/them being identified as "New" that they are then reported and labelled as "Backlog", until they no longer appear so they are shipped which I've explained below. 

Lastly in the same or new column I also need it to indicate if the same Part Number hasn't appeared in the latest reported date dataset but was in the previous reported date dataset then for it to be labelled as "Shipped" and its very important that the "Shipped" Part Numbers value when I create a matrix table is allocated to that weeks reported date not to the previous reported date. 
Please see below for clarification. 

[Deeks]

Hi @tamerj1, 
Thank you for your response.

I have tried to apply this to my larger dataset but have not received the results as expected (well, only partly). Screenshot below of what I expected. Actual data set is also shared below, with my attempt from previous answer.

actualDataSet  

[tamerj1]

@Deeks 
See attached ActualData file updated with the solution

[Deeks]

@tamerj1 
thank you for this but the shipped items are not marked correctly. An item should show as shipped if it no longer appears on this weeks list but did last week, i.e. the sales order number will be marked shipped today if last week it appeared on the list but doesn't today, regardless of whether it is new or backlog. 

[tamerj1]

@Deeks 
You need to provide screenshots with clear illustrations on them.

[Deeks]

One example is outlined below. 

for the 14th, it's currently showing as 0, but having had a look at the data, see below, it should total up to 223,619.37

So although those orders are new, they do not appear again on the 14th, and therefore, are shipped. 
Please also note, I have edited the ShippingAmount measure to -7 instead of +7 because the shipping dates were incorrect. I have attached this below. 

ActualDataSet 

[tamerj1 · Accepted solution]

[tamerj1]

Hi @Deeks 
Please refer to attached sample file with the proposed solution