Matching order ID from previous day to see yesterday's order value

[Anonymous]

Hi!

I am having trouble with writing the formula for this calculated column.

I have one big table that lists orders coming in by day. An order can remain "open" until the truck has finally shipped with all of the necessary materials that a consumer ordered. It is possible that an order's value decreases day over day while it is open if we are unable to fulfill all of the consumer's demand and we cut some materials off of the order.

Is there a way that I can input a calculated column that tells me the value of the order from yesterday by matching the specific order ID?

Thank you very much in advance for your help!!

[amitchandak]

You can create a column with diff from today and age 1 means open from yesterday

datediff = datediff(order[date],today(),day)

Can you share sample data and sample output.

[Anonymous]

Thank you for your quick response!

Unfortunately, I cannot share any data as it is highly confidential. I will try to make up an example table.

DATE	ORDER ID	Today's Value	Yesterday's Value
1/1/2020	123	$500	-
1/1/2020	456	$600	-
1/1/2020	789	$700	-
1/2/2020	123	$450	$500
1/2/2020	456	$600	$600
1/2/2020	789	$675	$700
1/3/2020	123	$450	$450
1/3/2020	456	$600	$600
1/3/2020	789	$675	$675

The "Yesterday's Value" column is what I am trying to compute.

Thank you!!

[amitchandak · Accepted solution]

One of two is working

Previous value = Minx(filter(table,table[DATE]=earlier(table[DATE])-1 && table[ORDER ID]=earlier(table[ORDER ID])),table[Todays Value])
Previous value = Minx(filter(table,table[DATE]-1=earlier(table[DATE]) && table[ORDER ID]=earlier(table[ORDER ID])),table[Todays Value])

[Anonymous]

Seem to me as it is working! Thank you very much for your help!!!