Many Instances of SKU - If One Instance Meets Criteria, Apply Result to All Instances

[bvbull200]

I'm hoping I can explain this properly.

I have a table that has a record of every transaction for every part in a given period.  Some of the transactions happen after a certain date and have that sale recorded in a second column.  Sales from before that date are not carried over.  This is already established in the table.  I need every part that has a sale after the given date to have an indication that it has met the selling criteria.  The current calculated column puts "SOLD" on every sale after the date and "NOT SOLD" on every sale before the date.  The issue with this is that it means that a part can be counted as both SOLD and NOT SOLD. 

What I currently have is below:

Part #	Sale	Sale After Date	Has Product Ever Sold After Date?
ABC	$20		NOT SOLD
ABC	$22	$22	SOLD
ABC	$18		NOT SOLD
XYZ	$100	$100	SOLD
XYZ	$95		NOT SOLD
123	$50		NOT SOLD
123	$48		NOT SOLD

Since two of these parts do have a sale after the date, I need them to all record as SOLD.  I am looking for the following result:

Part #	Sale	Sale After Date	Has Product Ever Sold After Date?
ABC	$20		SOLD
ABC	$22	$22	SOLD
ABC	$18		SOLD
XYZ	$100	$100	SOLD
XYZ	$95		SOLD
123	$50		NOT SOLD
123	$48		NOT SOLD

My initial hunch was some sort of a helper table off of the main query that aggregated all of the part #s by Sale After Date and if it was > $0, then give it "SOLD", but have just one row per part #, then join it to the main table, but I'm not sure how to acheive that solution or if there is a better one out there.  Do you have any suggestions?

I appreciate any insight.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(SUM(Data[Sale After Date]),FILTER(Data,Data[Part #]=EARLIER(Data[Part #])))>0,"Sold","Not sold")

Hope this helps.

[bvbull200]

I'm not exactly sure why this works, but it does!  Time to do some reading up on 'EARLIER'.  Thank you so much for the solution.  I have marked as accepted. 

[Ashish_Mathur]

You are welcome.