Forum Discussion

heygau's avatar
heygau
Helper I
2 years ago
Solved

Need help with DAX

Product Received - Product Sold = Stock Left on Hand (Each  month )

 

If we are left with 1000 stock on hand, that will carry over to the next month

(1000 + Receivals) - Sold = Stock on hand.

 

The opening balance of stock is 27192 as of July 2022. From this point, we need to calculate the stock on hand for each month.We have receivals and sales data for every month from July 2022. Thanks in Advance 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi heygau ,

    Thanks for Musadev  sharing. Please refer to my steps have a try.

    Stock on Hand =
VAR OpeningBalance = 27192
VAR CumulativeReceivals =
    CALCULATE (
        SUM ( Receivals[Quantity] ),
        FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) )
    )
VAR CumulativeSales =
    CALCULATE (
        SUM ( Sales[Quantity] ),
        FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) )
    )
RETURN
    OpeningBalance + CumulativeReceivals - CumulativeSales

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

     

4 Replies

  • Musadev's avatar
    Musadev
    Resolver III
    2 years ago

    Hi heygau 
    You will need to create 4 measures.
    Product Received,
    Product Sold,
    Stock on hand,
    Stock Left CM (Current Month)
    Stock Left PM (Previous Month)

    The last 2 will be the same for consecutive dates. 

    If you can share the insert statement of the sample data to oracle Database, i will share the measures script as well. 

     

  • heygau's avatar
    heygau
    Helper I
    2 years ago

    Thanks Musadev 

     

     

     

    I want to calculate
    Stock on hand =  [(Opening Balance + Bales Received) - Bales Sold]
    I have to consider the opening balance from June 2023 and should continue with the result value on the Stock on hand column. 



    Bales Received is a measure = SUM(Bales Received ) and Bales Sold as well, from multiple tables. Thanks.

    • Musadev's avatar
      Musadev
      Resolver III
      2 years ago

      Hi heygau 
      Can you please share the sample data in the Insert statement of Oracle DB? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi heygau ,

    Thanks for Musadev  sharing. Please refer to my steps have a try.

    Stock on Hand =
VAR OpeningBalance = 27192
VAR CumulativeReceivals =
    CALCULATE (
        SUM ( Receivals[Quantity] ),
        FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) )
    )
VAR CumulativeSales =
    CALCULATE (
        SUM ( Sales[Quantity] ),
        FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) )
    )
RETURN
    OpeningBalance + CumulativeReceivals - CumulativeSales

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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