Total stock per day, using todays value, planned refills and forecasted consumption

[Henrik_PBI]

Hello, all

I have a question that I need your help to solve.

What I have:

I have a dataset where I run a query everyday to get todays stock. I also have planned future consumption per day, and I have planned refills to the stock. See table below for example.

What I want to create:

I want to create a column that show total stocks per day ("Total stock" in yellow), which is based on todays stock, planned refills and consumption per day.

(Because I run the query for todays stock everyday, I want the table to allways start on todays date)

In excel I would have solved it like this, where "Total stock" for today (in cell E2) is calculated: B2+C2-D2,

     For tomorrow (E3): E2+C3-D3

     For the day after (E4): E3+C4-D4

     And so on:

Any suggestions on how to achieve this?

Thank you, I'm really grateful for all help 🙂

[DataInsights · Accepted solution]

@Henrik_PBI,

As I understand it, this is a running total of three columns. Here are two calculated columns. You can try both and use the more performant one.

Total Stock = 
VAR vDate = Table1[Date]
VAR vResult =
    CALCULATE (
        SUM ( Table1[Todays stock] )
            + SUM ( Table1[Planned refill] )
            - SUM ( Table1[Consumption per day] ),
        ALL ( Table1 ),
        Table1[Date] <= vDate
    )
RETURN
    vResult

Total Stock SUMX = 
VAR vDate = Table1[Date]
VAR vTable = FILTER ( Table1, Table1[Date] <= vDate )
VAR vResult =
    SUMX ( vTable,
        Table1[Todays stock]
            + Table1[Planned refill]
            - Table1[Consumption per day]
    )
RETURN
    vResult

[Henrik_PBI]

Thank you, @DataInsights  - the second one worked fine for me!