Weekly Inventory through Inventory movements column

[redhol]

Hi All,

Might be an easy one but I have yet to figure it. I am trying to display closing stock for each week for some inventory analysis that I am working on - I am struggling to get this value as I do not have a "Stock on Date" field our ERP but I do have a ledger of all our stock movements (sales, delvieries, returns etc. displayed as +1 / -1 and so on.

What I want to be able to do is have a table split by week and the table display the position up to the close of that week, example in the picture.

The problem I have at the moment is that my formula is calculating that week only and not the value up to that date (or week end in this case).

Hope this makes sense and any help is appreciated!

[v-jiascu-msft]

Hi @redhol,

Can you share a sample?

What's your formula for now? 

Are the 1 and 2 in the snapshot weeks?

Best Regards,

Dale

[redhol]

Hi Dale,

THanks for the reply - data for stock movements is shown below

And current formula is

Rolling Stock =
CALCULATE(
Sum(HR_StockMovements[MovementQty]),
Filter(
ALL('Calculations'[Date].[Date]),
'Calculations'[Date].[Date] <= MAX('Calculations'[Date].[Date])
)
)

The date formulas are in a seperate table as I have a few different tables that the dashboard is pulling from. 

And yes the 1 and 2 are weeks in the snapshot.

Many Thanks,

[v-jiascu-msft · Accepted solution]

Hi @redhol,

The cause could be the [Date] in the dates. Please try the formula below.

Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date]),
        'Calculations'[Date] <= MAX ( 'Calculations'[Date] )
    )
)

Rolling Stock =
CALCULATE (
    SUM ( HR_StockMovements[MovementQty] ),
    FILTER (
        ALL ( 'Calculations'[Date].[Date] ),
        'Calculations'[Date].[Date] <= MAX ( 'Calculations'[Date].[Date] )
    )
)

Best Regards,

Dale

[redhol]

That works perfect. 

Thanks for your help.