topic Re: Inventory Forecast Future in DAX Commands and Tips

Inventory Forecast Future

Anonymous — Sun, 25 Feb 2024 17:13:57 GMT

Hi All

I need help to figure out this calculation forecast inventory. I have an issue with the DAX calculation

this calculation in Excel

date 25-Feb is today's date with current stock by today
26-Feb onward is the projection that I need to calculate
Usage forecast is measured by the rolling average of the last 14 days
Ending stock by = Onhand + Order qty - Usage
Stock 26-Feb is offset from ending stock 25-Feb, and continues on the next date. In this formula, I cannot measure cause have an error "Circular Dependency"

Thank you in advance

Re: Inventory Forecast Future

Anonymous — Mon, 26 Feb 2024 03:05:54 GMT

Hi Anonymous

It seems like you're trying to create a forecast that takes into account the rolling average of the last 14 days to project future inventory levels.

The circular dependency error you're encountering typically occurs when a calculation refers back to itself directly or indirectly, creating a loop that Power BI cannot resolve.

The following ways may solve your problem:

First, ensure you have a measure that accurately calculates the rolling average of the last 14 days.

To calculate the ending stock for each day, you can create a measure that takes the previous day's ending stock and adjusts it based on the orders and the rolling average usage. However, to avoid circular dependency, ensure that each calculation is based on previously calculated or static values.

ENDING STOCK = CALCULATE( SUM('Table'[ON HAND FORECAST]) + SUM('Table'[ORDER QTY]) - SUM('Table'[USAGE FORECAST]), FILTER( ALL('Table'), 'Table'[DATE] = MAX('Table'[DATE]) ) )

Here is the result.

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

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

Re: Inventory Forecast Future

Anonymous — Tue, 27 Feb 2024 14:11:53 GMT

I apologize it is not working, let me elaborate again on the table
I need help to make the forecast in and out stock or inventory based on order qty and Usage,

date 25-Feb is today's date with current stock by today
Usage forecast is measured by the rolling average of the last 14 days
Ending stock by = Onhand + Order qty - Usage

I need projection inventory for 26-Feb onward for my PBI