Running total

[Zvekanm]

Hi ,

i have small issue with running total. 

Im calculating stock value in warehause from 2022 till today, and its working.

For quantities vorking with no mistake, but for value not.

Problem with value calculation is pricing changed on the begining 2023.

All part on stock has new price at 01.01.2023

To explain.

On 31.12.2022 a i have on stock 1 PCE with value 10Eur

 in January 2023 is consuption 1 PCE with new value 11Eur and my value stock is now-1 EUR

How to solve this issue?

[Zvekanm]

hi,

running total

Stock Value EUR =

VAR _result=

CALCULATE(

   [Starting Value]+[Total Value 101]+[Total Value 102]-[Total Value 122]+[Total Value 123]-[Total Value 261]+[Total Value 262]-[Total Value 711]+[Total Value 712]-[Total Value 291]+[Total Value 309]-[Total Value 601]+[Total value 602],

    FILTER(ALLSELECTED('DIM Calendar'[Transaction_Date]),

    'DIM Calendar'[Transaction_Date]<=MAX('DIM Calendar'[Transaction_Date])

    )

    )/117.5

    return _result

one of the meassure

Total Value 101 =

CALCULATE(

SUM('MSEG Material MvM'[Loc.curr.amount]),

filter('MSEG Material MvM','MSEG Material MvM'[MvT]=101)

)

depends of type of movement (101,102...)its adding or subtracts on stock value

Value is from extract table in SAP

[eliasayyy]

hmm looks complicated try 

CALCULATE(
   [Starting Value]+[Total Value 101]+[Total Value 102]-[Total Value 122]+[Total Value 123]-[Total Value 261]+[Total Value 262]-[Total Value 711]+[Total Value 712]-[Total Value 291]+[Total Value 309]-[Total Value 601]+[Total value 602],
    'DIM Calendar'[Transaction_Date]<=MAX('DIM Calendar'[Transaction_Date]
    )
    )/117.5

[Zvekanm]

@eliasayyy 

if you have more simple solution... then you are my best friend 😄

problem is that i must track stock value on weekly base of each material.

then material group... procurment type.. supplier..

lot of demands from my manager.

this is only solution that work for me.

plus i need every type of movement in other calculation..

[eliasayyy]

well i cant help out much without seeing datasets but if you just want to get the stock vlue per week , powerbi automatically does that 
your measure above will sum up all values until today example if yesterday i had 10$ and today i had 30$ the measure will return 
10 on yesterday and 40 on today if you just want to return the price per day just use a simple max function 

[Zvekanm]

and top of that...

Forecast..

addind each PO to stock for forecasting stock value in next period - consuption 

😄

easy task for me

[eliasayyy]

please provide with more information please. share measures , your pbix or a sample dataset explaining what you want