Forum Discussion
DAX: Perfom calculation on virtual tables
Community SupportHi JonathanOTM ,
Sorry I'm mot very clear about your description, could you please provide a sample in table format to explain it more.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sure,
Table Sales Order Archive:
Date Archive Version no. SO-Nr Item Nr Qty Remaining Qty Qty Shipped Customer Nr 01-01-2022 1 SO00001 I00001 20 20 0 C000001 01-01-2022 1 SO00001 I00002 30 30 0 C000001 05-01-2022 1 SO00002 I00002 40 40 0 C000002 10-01-2022 2 SO00001 I00001 20 20 0 C000001 10-01-2022 2 SO00001 I00002 35 35 0 C000001 15-01-2022 3 SO00001 I00001 20 0 20 C00001 15-01-2022 3 SO00001 I00002 35 0 35 C00002 19-01-2022 2 SO00002 I00002 40 0 40 C00002 Table Output
Posting Date Document Nr Entry Type Item Nr Qty 14-01-2022 PO00001 Output I00001 20 14-01-2022 PO00002 Output I00002 35 18-01-2022 PO00003 Output I00002 40 What I would like to do is add a column in the output table which contains the outstanding qty on my Sales Orders on that sprecific day for the item that was produced. In my table Sales Order Archive would first need to (1) filter on Date archived < Output Date, (2) Filter on [Sales Order Archive].Item No = [Output].Item No then (3) Only take the latest version No. for each Sales Order and eventually sum the outstanding qty.
Ex.
Posting Date Document Nr Entry Type Item Nr Qty Outstanding Qty 14-01-2022 PO00001 Output I00001 20 20 14-01-2022 PO00002 Output I00002 35 75 18-01-2022 PO00003 Output I00002 40 40 The value of outstanding Qty on 14-01-2022 for I00002 would come from:
Table Sales Order Archive:
Date Archive (Filter on <15/01/2022) Version no. SO-Nr Item Nr (Filter on I00002) Qty Remaining Qty Qty Shipped Customer Nr 01-01-2022 1 SO00001 I00002 40 40* 0 C000001 05-01-2022 1 SO00002 I00002 40 40 0 C000002 10-01-2022 2 SO00001 I00002 35 35 0 C000001 TOTAL Σ = 35 *as the first row is not the latest version of Sales Order SO00001, it not taken into account. The version no. 3 of SO00001 is not taken into account as the date archived is AFTER the ouput Date.
