How to calculate purchase and sales stock information from two columns into one separate column?

[bkoenen]

Hello,

I think I have a very simple question but I can't figure it out, I have a table with purchase and sales with stock quantity. I want to create a separate column that calculates the daily stock value. But how can I get a Total stock result like the column  "Total Stock Good" in my example, I just created that to makes things clear.

With kind regards,

Björn

[smpa01]

@bkoenen you would need an index column and then you can either do a calulcated column like this

Column =
VAR _purchase =
    SUMX (
        FILTER (
            'Table',
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && 'Table'[Article] = EARLIER ( 'Table'[Article] )
        ),
        'Table'[Purchase QTY]
    )
VAR _sales =
    SUMX (
        FILTER (
            'Table',
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && 'Table'[Article] = EARLIER ( 'Table'[Article] )
        ),
        'Table'[Sales QTY ]
    )
RETURN
    _purchase - _sales

or a measure like this

Measure =
VAR _filter =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Index] <= MAX ( 'Table'[Index] )
            && 'Table'[Article] = MAX ( 'Table'[Article] )
    )
VAR _purchase =
    CALCULATE ( SUM ( 'Table'[Purchase QTY] ), _filter )
VAR _sales =
    CALCULATE ( SUM ( 'Table'[Sales QTY ] ), _filter )
RETURN
    _purchase - _sales

[bkoenen]

Thank you for the code, I copied it and it works.

Kind regards Björn

[YukiK · Accepted solution]

There may be other solutions, but I'd get cumulative for both purchase and sales qty and then subtract cumulative sales from cumulative purchase. The following measure should work:

My Measure =
VAR __SalesRunningTotal =
CALCULATE (
SUM ( 'financials'[ Sales] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
VAR __DiscountRunningTotal =
CALCULATE (
SUM ( 'financials'[Discounts] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
RETURN
__SalesRunningTotal - __DiscountRunningTotal

Using power bi sample data

Please give it a thumbs up if this helps!

[amitchandak]

@bkoenen , Try a new columns

Sumx(filter(sheet, [Article] = earlier([Article]) && [delivery Date] <= earlier([delivery Date])),[Purchase Qty] - [Sales Qty])