Calculate stock according to purchases and sales

[Syndicate_Admin]

Hello, and thank you in advance to anyone who can help me.

I have a stock of products from the store, from a gas station.

There are purchases and sales. I need a column that gives me the total which is: 1st purchase - SALES + PURCHASES. Here is a sample excel:

DATE	SHOPPING	SALES	STOCK
14/6/2024	24		24
15/6/2024		1	23
17/6/2024		1	22
17/6/2024		2	20
19/6/2024		1	19
19/6/2024		1	18
19/6/2024		1	17
3/7/2024		1	16
5/7/2024		1	15
7/7/2024		2	13
10/7/2024		1	12
24/7/2024	48		60
9/8/2024		2	58

I need to calculate a column that the stock has so that I can then value it. The date should not matter unless it is searched as a starting point and a variable can be saved with the initial stock.

The MTDTODAY, QTDTODAY, and YTDTODAY functions are of no use to me because the calculation starts again when the period of the used formula ends

Thanks a lot

Gears

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[marcelsmaglhaes]

@Syndicate_Admin 

Taking a look at the question and considering only the scenario presented, the measure to calculate the stock would be:

stock_measure =

VAR CurrentDate = MAX('Table'[DATE])

RETURN

CALCULATE(

    VAR LastStock = CALCULATE(

        [Estoque],

        FILTER(

            ALL('Table'),

            'Table'[DATE] < CurrentDate

        )

    )

    VAR CurrentShopping = SUM('Table'[SHOPPING])

    VAR CurrentSales = SUM('Table'[SALES])

    RETURN

        IF(

            ISBLANK(LastStock),

            CurrentShopping,

            LastStock + CurrentShopping - CurrentSales

        ),

    FILTER(

        ALL('Table'),

        'Table'[DATE] <= CurrentDate

    )

)

[Syndicate_Admin]

Hello, thanks for your help but what I need to chalk up is the STOCK column.

[marcelsmaglhaes]

You need a calculated column? If yes, then the DAX is:

STOCK_COLUMN =

VAR CurrentDate = 'Table'[DATE]

RETURN

CALCULATE(

    SUM('Table'[SHOPPING]) - SUM('Table'[SALES]),

    FILTER(

        ALL('Table'),

        'Table'[DATE] <= CurrentDate

    )

)

[Syndicate_Admin]

Hello, I wanted to say that I need a new measure that calculates the column that I called Stock where I take the 24 initial products (the first purchase) and add the new purchases and subtract the sales.