Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX Formula: Inventory QOH in reverse

Disclaimer: I just started working with DAX and I've read and watched a ton of content but I'm still getting hung up on very simple problems.  I learn faster if I can see the solutions to the problems in my head.

 

I have an inventory table which holds all item specifics: Quantity on Hand, PAR, Location, etc.

I also have a transaction table that shows all of the (+/-) transactions that happened to any given item.

 

Instead of simply calculating a units in stock like:

 

UnitsInStockSimple =
CALCULATE (
    SUM ( 'Transaction Data'[Qty] ),
    FILTER (
        ALL ( 'Date Table'[Date] ),
        'Date Table'[Date] <= MAX( 'Date Table'[Date] )
    )
)

 

I want to work in reverse and pick up the QOH field from the Inventory table and work backwards through the dates.  The final result should be the QOH on for any given date:

 

Example for item 123

Current QOH is 400

Yesterday (during 1 day) there were 3 transactions in desc order: -3 (distribution), -1 (Adjustment), +6 (order receipt)

I would like to see the QOH for each date: 403, 404, 398

 

Because: If the last transaction subtracted 3 and today's QOH is 400, then the QOH before the transaction must have been 403.

15 REPLIES 15
Nhk22
Regular Visitor

Hi @Anonymous  had u found the solution? I have to built similar to count stock on hand quantity backwards based on FIFO receipts transactions. 1st table show SOH qty for the month, and 2nd table have receipts date and quantity for the material item at company level.  How should I connect these two tables? Thanks for the team advise. 

Hi,

Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

1st table : stock on hand position

Company CodePlantMaterialSOH QuantityStock on Hand per PlantFIFO Quantity
FR0290601000640131,349.000852.000220.421
FR0290411000640131,349.000497.000128.579

 

2nd table: receipt transactions with aging bracket

Company CodePlantMaterialAgeRcpt Dateobs range & % Receipt Quantity
FR029060100064013630-Nov-220 to 11 month  0 %1000
FR0290411000640132120-Aug-2112 to 23 mths 25%525
FR0290411000640135130-Jul-21> 24 months 50%1990
FR02904110006401311130-May-21> 24 months 50%2000

 

output expected: Obsolescene is Calculated at Company Code Level based on FIFO Aging Methodology. To compute SOH backward based on latest FIFO receipt tranactions that made up the ending SOH. 

Source:  3. obs receipt aging extract (show applicable only)
 SOH Quantity 2 Last receipt dateReceipt QtyReceipt  ageing bracket
 1000 Jun-2110000%
balancing figure349 Aug-2052525%
SOH Quantity :1349    

Hi,

I cannot undestand from the 2 tables that you have pasted.  Would it be possible to put this data in an MS Excel workbook and explain the result with formulas/text boxes.

I still do not know how much i can help but i would like to try.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

I haven't tested this but have you tried reversing the operator?

 

UnitsInStockSimple =
CALCULATE (
    SUM ( 'Transaction Data'[Qty] ),
    FILTER (
        ALL ( 'Date Table'[Date] ),
        'Date Table'[Date] >= MAX( 'Date Table'[Date] )
    )
)
 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Thanks,

 

I will try that in the morning at work.  The original problem with that formula is that it doesn't pull 'Inventory Data'[QOH] at all.  The transactions are in: 'Transaction Data' [Qty].  

 

Also, that formula only shows me the total per date, while the sum of all totals does accurately reflect the current QOH, that's not entirely what I want.  I just want to be able to show a date and show the QOH for that date/item. (Maybe I'm displaying that measure incorrectly?)

 

Hi @Anonymous,

 

Could you try the formula(untested) below to see if it works in your scenario? Smiley Happy

 

Inventory QOH =
VAR currentQOH =
    MAX ( 'Inventory Data'[QOH] )
VAR maxDate =
    CALCULATE ( MAX ( 'Date Table'[Date] ), ALL ( 'Date Table' ) )
VAR currentDate =
    MAX ( 'Date Table'[Date] )
RETURN
    currentQOH
        - CALCULATE (
            SUM ( 'Transaction Data'[distribution] )
                + SUM ( 'Transaction Data'[Adjustment] )
                + SUM ( 'Transaction Data'[order receipt] ),
            FILTER (
                ALL ( 'Date Table'[Date] ),
                'Date Table'[Date] >= currentDate
                    && 'Date Table'[Date] <= maxDate
            )
        )

 

Regards

Anonymous
Not applicable

test 4020.pngRelationships.pngtest 4044.png

Thanks, I learned a lot from that formula.  I assume it was meant to be a measure but I tried both and the result is always 12,955,306 for every date and every item.  It appears it might be adding up all QOH in my Inventory Data table?

 

Also, the only field I have qty in is the 'Transaction Data' [Qty]. there is another field: 'Transaction Data' [Transaction Type] which captures "adjustment", "Receipt", etc...  I changed the 3 lines on your formula to : SUM ( 'Transaction Data'[Qty] ),

 

 

Hi @Anonymous,

 

Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Anonymous
Not applicable

I would like to restart this ask in hopes that I will explain it better.

 

I have a perpetual inventory system where the program records all the previous transactions in a [Transaction] Table.  It also records all of the item inventory information including the Current QOH in an [Item] table. Below is a quick sample of the 2 tables combined.

 

The calculation that I need is the QOH for the previous transactions (i've added them in using a formula: Last Known QOH - Current Transaction = QOH after Transaction occured.)  IE: QOH for Apples is 10 and is stored in the [Item] Table.... 10 - 10 = 0.. so the previous QOH must have been 0 after the transaction occured.  Moving in reverse... 0- (5) = +5...

 

I would like this value to be in a column (in the Transaction Table) so that I can graph it and run measures against it.. IE: How many days have elapsed since the QOH was 0?... etc...

 

There are about 1.5 million lines and 300k items.  Would this make more sense to do in SQL prior to data pull? (not sure how'd I'd do that either)  Or would M code be a better candidate?  

 

Here is the example.... The last number in the QOH column (for each item) is the known QOH from the [Item] Table.  The remainder QOH numbers before it are what I need to calculate.

CategoryDate/Time          Qty changeQOH
Apples11/13/18 1:00PM-210
Apples11/13/18 2:00PM-37
Apples11/13/18 3:00PM-25
Apples11/13/18 4:00PM-50
Apples11/13/18 5:00PM1010
Banannas11/12/18 6:00AM-30100
Banannas11/12/18 8:00AM-3070
Banannas11/13/18 1:00PM-3040
Banannas11/13/18 2:00PM60100
Banannas11/13/18 3:00PM-3070
Banannas11/13/18 4:00PM-3040
Banannas11/13/18 5:00PM-3010
Oranges11/12/18 6:00AM-100100
Oranges11/12/18 8:00AM100200
Oranges11/13/18 1:00PM-100100
Oranges11/13/18 2:00PM100200
Oranges11/13/18 3:00PM-100100
Oranges11/13/18 4:00PM100200
Oranges11/13/18 5:00PM-100100

 

I've looked everywhere for this solution and I've tried several iterations of "Earlier" to get it to work but I'm still having trouble.  The closest I can find that somewhat resembles what I want to do is:

 

https://community.powerbi.com/t5/Desktop/Inventory-turnover-by-month/m-p/410252/highlight/true#M1885...

by @v-qiuyu-msft

Anonymous
Not applicable

I decided to take a shot... Using the data you provided I had to add a row for each category as a "beginning" balance.  From there created an index using EARLIER (well, variables defined before the calculation which is the same as using EARLIER)

Index = 
VAR CurrentCategory = Table1[Category]
VAR CurrentDate = Table1[Date/Time]
RETURN

CALCULATE(
    COUNTROWS(
        FILTER ( ALL ( Table1),
        CurrentCategory = Table1[Category]
        && CurrentDate >= Table1[Date/Time]
        )
    )
)

That tells me how many rows are less then or equal to the current row's data/time, and in the same category:

Index Colum.png

 

So then I can use that as a reference point of what came before the current row:

QOH = 
 VAR CurrentCat= Table1[Category]
 VAR CurrentIndex = Table1[Index]
 VAR CurrentQtyChg= Table1[Qty change]

RETURN
CALCULATE(
    SUM ( Table1[Qty change]),
       FILTER(
            Table1,             
            CurrentCat = Table1[Category]
                && CurrentIndex >= Table1[Index]
        )
 ) 

Then the final output:

Final Table.png

 

Maybe what you were looking for? 

Anonymous
Not applicable

Thanks for the reply!  I like the addition of an index and that may be the key.  There are too many items and transactions to consider inserting a row manually. 


What if we reverse the sort order so index:1 is the most recent date with the known QOH.  Would this eliminate the need for a new row?  I still don't know how that would look....

 

IndexItemDate/TimeQty ChangeQOH 
1Apples11/13/2018 17:001010 
2Apples11/13/2018 16:00-5?10-10=0
3Apples11/13/2018 15:00-2?0-(5)=5
4Apples11/13/2018 14:00-3?5-(2)=7
5Apples11/13/2018 13:00-2?7-(3)=10

 

maybe...

QOH = IF index = 1, then use QOH from Item Table,

Calculate( QOH - Qty Change, Index-1)

 

??  Does that make sense?

Anonymous
Not applicable

Sorry, not entirely clear.  Nothing is being inserted manually, just two calculated columns set up once and then that's all.  

Anonymous
Not applicable

Thanks @v-ljerr-msft I sent you a private message with some options that I have to directly exchange files but most all types of file sharing sites/functions are turned off in my organization.

I have some issue related to inventory is there a problem to share me the Pbix file in private email? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.