Projected Stock on Hand Expression Help

[fatconductor]

Hi there,

I am trying to get a projected on hand formula to work in my dataset. I've created in excel and almost replicated it in PBI, but am running into some issues as i'm a bit new to PBI.

The goal is to have a column where Network Stock on hand + On Order - Units Sold = Projected Stock on hand. See attached screen from an excel pivot table displaying the stock on hand future projection in green.

I've managed to translate all of the data and more to PBI, but am struggling with the DAX? expressions to get a forward calculation to work. After doing some research i kind of copied a formula found on these boards (sorry i don't have the link anymore) below.

PROJ ON Hand =

IF(DC_PowerBI[Time Data.Week E] >= date(2022,02,21),

VAR ExpectedChange =

CALCULATE(

sum(DC_PowerBI[On Order])-sum(DC_PowerBI[Units Sold]),

FILTER(

ALL('Time Data'[Week E]),

'Time Data'[Week E]<= MAX('Time Data'[Week E])

)

)

VAR Stock =

CALCULATE(

DC_PowerBI[Network SOH running total in Week E],

FILTER(

ALL('Time Data'[Week E]),

'Time Data'[Week E]<= MAX('Time Data'[Week E])

)

)

RETURN

ExpectedChange + Stock)

If I don't have the date filter the formula appears to run fine through the history projecting accurate stock levels to what actually occured, however once the NETWORK SOH field hits zero (current day) then the projection only serves as a "unit movements", summing On order and units sold, see below.

All of my data is in the one table including item, location, units sold, network soh, on order and now PROJ ON hand.

Can anyone help me to have the forward Proj ON hand column use it's previous result in the calculation so that it can correctly display forward SOH? Let me know if there's any other detail needed.

[Anonymous]

Hi @fatconductor ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[fatconductor]

Hi Evelyn,

I've just dropped you a sample file and my pbix file.
My ultimate goal here is to create a "Projected Stock" value that equals Network SOH - Actual/Forecast + On order. This column would move into the future as as far as my date table is setup.

Excel example below:

I've tried to reformat my data in a number of ways and a number of other peoples supposed solutions with the closest I got sort of copying this solution from Reddit.

I'm stumped, creating a future inventory projection and having issues adding known values and projec...

I did try to pivot my data out so that the measures were their own column but after receiving the the true dataset it is far too big (500mb) and cant be loaded so I need to keep the original dataset untouched (or as much as possible)

The end goal can be a measure if that's easier I just want it plotted on the chart and matrix as below.

Thanks again I really do appreciate the help.

[Anonymous]

Hi @fatconductor ,

Based on this: If I don't have the date filter the formula appears to run fine.You may try to replace ALL() with ALLSELECTED() 

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly

How to provide sample data in the Power BI Forum

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