Calculate Open Balance (Every Day)

[nightday]

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 · Accepted solution]

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]

I got the result, Thanks 

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

[Anonymous]

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]

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 

[djurecicK2]

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.