Trying to create a new column to track inventory levels

[rcb0325]

Hello! I am trying to create a new column in the "fact inventorytransactions" table that will show the current inventory level, and calculate the past inventory levels based on transactions. 

In the table below, I have the 'dim Date'[ActualDate], the 'fact InventoryTransactions'[Quantity], the 'fact InventorySummary'[OnhandQty], and the new column 'fact InventoryTransactions[InventoryLevel]

My thought is that by adding the current OnHandQty to the Quantity, I can create a column with the OnHandQty by date. The OnHandQty is not a hard coded value, but is updated daily with the current inventory value. 

Ex:

11/13/2024: InventLevel = 151 

11/12/2024: Invent Level = 151 + 37

11/11/2024: Invent Level = 151 + 37 + 152

Below is a snapshot of the table, and then how those columns are related. There are no calculated measures, other than the "InventoryLevel" column, which is what I am trying to calculate. All values are exported from D365. 

There is a relative date filter on the page that filters to 'dim Date'[ActualDate] is within the last 12 months (not including today) (11/19/2023 - 11/18/2024) 

Thanks in advance!

[Anonymous · Accepted solution]

Hi @rcb0325 

Thank you very much Jai-Rathinavel for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

“dim Date”

“fact InventoryTransactions”

“fact InventorySummary”

Create measures.

First, get the absolute value of the [Quantity] column.

ABS Quantity = ABS(SELECTEDVALUE('fact InventoryTransactions'[Quantity]))

Then,

InventoryLevel = 
VAR CurrentDate = SELECTEDVALUE('dim Date'[ActualDate])
VAR maxDate = CALCULATE(MAX('dim Date'[ActualDate]), ALL('dim Date'))
VAR CurrentOnHandQty = 
    CALCULATE(
        MAX('fact InventorySummary'[OnhandQty]),
        FILTER(
            ALL('fact InventorySummary'),
            'fact InventorySummary'[Date] = maxDate
        )
    )
VAR PreviousTransactions = 
    SUMX(
        FILTER(
            'fact InventoryTransactions',
            'fact InventoryTransactions'[TransactionDate] > CurrentDate
        ),
        'Measure'[ABS Quantity]
    )
RETURN
IF(
    SELECTEDVALUE('fact InventoryTransactions'[TransactionDate]) = maxDate,
    CurrentOnHandQty,
    CurrentOnHandQty + PreviousTransactions
)

Here is the result.

Regards,

Nono Chen

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

[Jai-Rathinavel]

Hi @rcb0325 , Can you send the expected output, visualized in a excel table?

[rcb0325]

Here is a copy of the excel table, that has the Inventory Level column calculated correctly. 
151 is the current inventory level (changes daily). I also believe it is the sum of ALL transactions for ALL dates.

Each previous day needs to subtract the transaction qty for the day following it. 

The Actual Date is only showing for days that there was an inventory transaction made

ActualDate	Sum of Quantity	Sum of OnHandQty (current day)	Sum of InventoryLevel
11/13/2024	-37	151	151
11/12/2024	-152	151	188
11/7/2024	0	151	340
11/1/2024	-5	151	340
10/25/2024	-11	151	345
10/23/2024	-161	151	356
10/22/2024	-100	151	517
10/21/2024	617	151	617
7/24/2024	-2	151	0
7/1/2024	-29	151	2
6/28/2024	-35	151	31
6/27/2024	-45	151	66
6/26/2024	-50	151	111
6/20/2024	-10	151	161
6/17/2024	-35	151	171
6/13/2024	-120	151	206
6/12/2024	-18	151	326
6/10/2024	-73	151	344
6/7/2024	-268	151	417
6/6/2024	-234	151	685
6/5/2024	-100	151	919