Calculated Column to find date where the sum of units meets certain value

[Anonymous]

Delivery_Table: Date a delivery was made and the number of units delivered. Table includes maximum delivery capacity.

Arrival_Units = ( Delivery_Table[Capacity] - Delivery_Table[UnitsDelivered] )

Transaction_Table: Date and number of units sold

On the Delivery_Table I want to make a calculated column called *Delivery_Table[Arrival Units Expired Date] where the Transaction_Table[Transaction Date] >= Delivery_Table[Delivery Date] && sum of Transaction_Table[Units Sold] >= [Arrival_Units] and returns the Transaction_Table[Transaction_Date] this occurs.

Delivery_Table
Delivery Date	Capacity	Delivered Units	Arrival Units	*Arrival Units Expired Date
9/1/2022	100	75	25	9/6/2022

Transaction Table
Transaction Date	Units Sold
9/1/2022	5
9/2/2022	1
9/3/2022	4
9/4/2022	5
9/5/2022	5
9/6/2022	5
9/7/2022	3

[Anonymous]

@v-yueyunzh-msft I'm unable to figure out how to attach an example file. Your example looks fine just need to add 'Location ID'. Please note that the delivery_table has 3 million rows and the transaction_table has 17 million rows. I did try and run your original code but, was met with the below error. 

 <ccon>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

You can use this dax , then you can add the "Location ID" condition.

*Arrival Units Expired Date = var _cuurent_arr='Delivery_Table'[Arrival Units]
var _current_deliver_date = 'Delivery_Table'[Delivery Date]
var _cuurent_ID = 'Delivery_Table'[Location ID]
var _t= FILTER('Transaction_Table' , 'Transaction_Table'[Transaction Date] >=_current_deliver_date && 'Transaction_Table'[Location ID] =_cuurent_ID) 
var _t2= ADDCOLUMNS(_t , "sum" , SUMX(FILTER(_t , [Transaction Date] <=EARLIER('Transaction_Table'[Transaction Date])) , [Units Sold]))
var  _t3 =FILTER(_t2 , [sum] >= _cuurent_arr)
return 
MINX(_t3,[Transaction Date])

And i use the new test data, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

@v-yueyunzh-msft Thank you for your reply. I'm confident this will work however, I failed to mention in my previous post there is a 'Location ID' to match to on both tables. Secondly, the next column I'll be making would be 'Delivered Units Expired Date'. I'm not fully grasping the ADDCOLUMNS within the variables so, helping me with both would help me get there. Below is the example tables....

[v-yueyunzh-msft]

Hi , @Anonymous 

Here are the steps you can refer to :

(1)This is my test data:

(2)We can create a calculated column in 'Delivery_Table' :

*Arrival Units Expired Date = var _cuurent_arr='Delivery_Table'[Arrival Units]
var _current_deliver_date = 'Delivery_Table'[Delivery Date]
var _t= FILTER('Transaction Table' , 'Transaction Table'[Transaction Date] >=_current_deliver_date) 
var _t2= ADDCOLUMNS(_t , "sum" , SUMX(FILTER(_t , [Transaction Date] <=EARLIER('Transaction Table'[Transaction Date])) , [Units Sold]))
var  _t3 =FILTER(_t2 , [sum] >= _cuurent_arr)
return 
MINX(_t3,[Transaction Date])

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly