Virtual Table within a Calculated Column

[rsbin]

Good Afternoon,

Looking for some assistance with a Calculated Column dealing with Inventory.

Have a Table of On Hand Inventory.

Facility	Item	Pre-AdjustmentDate	OnHandPreAdjustment	Adjustment Date
ABC	ATA235	04/30/2023	1	05/01/2023

Have a Transaction Detail Table (simplified) as follows:

Facility	Item	Transaction Type	Date	Qty.
ABC	ATA235	Inventory Adjustment	05/01/2023	-1
ABC	ATA235	Inventory Adjustment	05/01/2023	1

Want to add a Calculated Column to the first table that sums the Qty in the second table for each Facility, Item and Date match.

I have been able to do it by creating a Summary Table and Lookup. 

Facility	Item	Pre-AdjustmentDate	OnHandPreAdjustment	Adjustment Date	Adjustment
ABC	ATA235	04/30/2023	1	05/01/2023	0

Thinking there is a more efficient way by creating a virtual table within a Calculated Column, but haven't been able to get it to work.  Any guidance would be much appreciated.

Regards,

[jgeddes · Accepted solution]

Something like this might work...

Adjustment =

var _vTable =

FILTER(

    SUMMARIZE(

        txTable,

        txTable[Item],

        txTable[Facility],

        txTable[Date],

        "sumQTY", SUM(txTable[Qty.])

    ),

    txTable[Item] = onHandTable[Item] && txTable[Facility] = onHandTable[Facility] && txTable[Date] = onHandTable[Adjustment Date]

)

Return

SUMX(

    _vTable,

    [sumQTY]

)

[rsbin]

@jgeddes ,

Thanks much for the prompt reply.

At first glance, appears to be what I am looking for.  Will give it a shot later today and let you know how I make out.

Thanks again!

Regards,

[rsbin]

@jgeddes ,

Just wanted to let you know that I got it to work as expected.  Thanks again for the assist.

Regards,