topic Re: DAX Calculated Column Creation for Inventory Tracking in DAX Commands and Tips

DAX Calculated Column Creation for Inventory Tracking

WILFR3D2K24 — Tue, 13 Feb 2024 23:35:50 GMT

Hi everyone,

I'm currently facing some difficulty in generating a calculated column using DAX, whereas it's relatively straightforward using Excel.

1. Below is an image displaying a sample of the dataset I'm currently working with:

This dataset illustrates the inventory of two products. I'm attempting to monitor the remaining units after each shipment.

2. The new column, named "Quantity," is what I'm aiming to create using DAX.

3. Here is the simple Excel formula that achieves the desired output:

The logic needs to take into consideration there multiple items within Category, so it can't simply substract the shipment/ or add the stock replenishment from the above value, this is where I suspect the CategoryIndex will come in handy.

Ideally, I would prefer a solution in DAX. However, if it can be accomplished using M Code, I'm also open to suggestions.

Re: DAX Calculated Column Creation for Inventory Tracking

Jihwan_Kim — Wed, 14 Feb 2024 04:14:10 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

WINDOW function (DAX) - DAX | Microsoft Learn

Quantity CC = SUMX ( WINDOW ( 1, ABS, 0, REL, Data, ORDERBY ( Data[CategoryIndex], ASC, Data[Date], ASC ), , PARTITIONBY ( Data[Category] ), MATCHBY ( Data[CategoryIndex], Data[Date] ) ), Data[Stock Replenishment] + Data[Shipments] )

Re: DAX Calculated Column Creation for Inventory Tracking

WILFR3D2K24 — Wed, 14 Feb 2024 10:07:52 GMT

Thank you, this worked!