Forum Discussion

heygau's avatar
heygau
Icon for Helper I rankHelper 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.  ...
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.

     

     

     

     

     

Musadev's avatar
Musadev
Icon for Resolver III rankResolver 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.