Need help with DAX

[heygau]

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 

[Anonymous · Accepted solution]

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.

[heygau]

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]

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

[Musadev]

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.