Forum Discussion

nightday's avatar
nightday
Regular Visitor
3 years ago
Solved

Calculate Open Balance (Every Day)

HI BRO 

I would be get the open stock every day, but I dont know what formula should using in Power BI, can you please help 

 

on 22 Nov 2022 

 

Daily Transaction (Positive is In -house, Negative is Out House )

Trace back the Opening Balance of Each date 



  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi nightday ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

    RESULT =
VAR _2 =
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= SELECTEDVALUE ( 'Table'[date] )
                && 'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
        )
    )
VAR _NEWTATA =
    CALCULATE (
        MAX ( 'current stock'[QTY] ),
        FILTER (
            ALL ( 'current stock' ),
            'current stock'[item] = SELECTEDVALUE ( 'Table'[item] )
        )
    )
RETURN
    _NEWTATA - _2

    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 _ Polly

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

5 Replies

  • djurecic's avatar
    djurecic
    Icon for Super User rankSuper User
    3 years ago

    Hi nightday ,

     Here is an approach

    Create a summary table on Daily Transactions to sum QTY.

     

    Somethng like this=

    SummaryTransactions= Summarize('Daily Transactions', 'Daily Transactions'[ITEM],'Daily Transactions'[Date],"Qty", SUM('Daily Transactions'[Qty]))

     

    Then create a relationship between this new table and Current stock table based on Item field.

     

    Then use RELATED function to create a new calculated column in the summary table with the stock QTY, then subtract the 2 values. Then create a matrix visual and use month as a column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi nightday ,

    How to get the value 50 on date: 2022/11/19 and item ABC? In the Daily Transaction, the result is 10.

     

    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 _ Polly

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

    • nightday's avatar
      nightday
      Regular Visitor
      3 years ago

      ITEM : Current Stock 60 on 22 Nov  -20 on 21 Nov therefore, result is 80 on 21 Nov ,then 20 on 20 Nov, result is 60 on 20 Nov, then 10 on 19 Nov, result is 50 on 19 Nov 

      Now I  need to Trace back the opening balance on each date 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi nightday ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

    RESULT =
VAR _2 =
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= SELECTEDVALUE ( 'Table'[date] )
                && 'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
        )
    )
VAR _NEWTATA =
    CALCULATE (
        MAX ( 'current stock'[QTY] ),
        FILTER (
            ALL ( 'current stock' ),
            'current stock'[item] = SELECTEDVALUE ( 'Table'[item] )
        )
    )
RETURN
    _NEWTATA - _2

    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 _ Polly

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

    • nightday's avatar
      nightday
      Regular Visitor
      3 years ago

      I got the result, Thanks 

      but if some date is missing, how can I got the result like below