Calculating stock day over day

[Sandritto]

Hi everyone,

I was trying to move my Order Schedule on to Power BI. I already have my querys from the database onto Power BI, but can´t figure out the correct measures.

This screenshot is what I use at the moment. Simple outdated excel made manually and few formulas.

As you can see formulas in Excel are:

• Final Stock = Initial Stock + Offer - Demand
• Initial Stock = Previous Day Initial Stock

Trouble comes transforming this formulas in to Power BI measures.

Any advice?

[Icey · Accepted solution]

Hi @Sandritto ,

Try this:

1. Create a column, which is the first value of "Initial Stock".

Column = 23.5

2. Create columns.

Measure 1 = MAX ( Table2[Offer] ) - MAX ( Table2[Demand] )

Measure 2 =
SUMX (
    FILTER ( ALLSELECTED ( Table2 ), Table2[Date] <= MAX ( Table2[Date] ) - 1 ),
    [Measure 1]
)

Initial Stock = MAX([Column])+[Measure 2]

Final Stock =
VAR m =
    SUMX (
        FILTER ( ALLSELECTED ( Table2 ), Table2[Date] <= MAX ( Table2[Date] ) ),
        [Measure 1]
    )
RETURN
    m + MAX ( [Column] )

PBIX file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nikshri]

Can I get the PBI file of this solution?