Start Stock and End stock line by line

[YoannC]

Hello everyone,

I need your help.

i've this kind of table where start stock and end stock need to be calculate.

Line	Week	Start Stock	sales	return	End Stock
1	45	6	22	18	2
2	46	2
3	47

- Start Stock is 6 for first line and after it's the value on the previous line of end stock.

- End Stock is Start Stock - Sales + Return of each line.

I try a lot of things 😞

Thanks for your Help 🙂

[gmsamborn · Accepted solution]

Hi @YoannC 

Try these measures and take a look at the example pbix below.

Sales = SUM( 'Table'[Sales] )

Returns = SUM( 'Table'[Returns] )

Start Stock = 
VAR _1stWkStock =
    CALCULATE(
        SUM( 'Table'[Start Stock] ),
        'Table'[Week] = MIN( 'Table'[Week] )
    )
VAR _CurrWk = SELECTEDVALUE( 'Table'[Week] )
VAR _PrevWkTD =
    CALCULATE(
        [Returns] - [Sales],
        FILTER(
            ALLSELECTED( 'Table' ),
            'Table'[Week] < _CurrWk
        )
    )
VAR _Result = 
    IF(
        HASONEVALUE( 'Table'[Week] ),
        _1stWkStock + _PrevWkTD
    )
RETURN
    _Result

End Stock = [Start Stock] + [Returns] - [Sales]

Start-stock and End-stock.pbix

Since DAX can't easily look up the previous row like Excel, these calculations are done differently.  So [Start Stock] can't refer to [End Stock] since [Start Stock] is used in the calculation of [End Stock]. (circular)

I'm really having a tough time putting this into words.

Maybe somone else can help with the explanation.