Inventory Forecast Future

[ryanrizz]

Hi All

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

this calculation in Excel

1. date 25-Feb is today's date with current stock by today
2. 26-Feb onward is the projection that I need to calculate
3. Usage forecast is measured by the rolling average of the last 14 days
4. Ending stock by = Onhand + Order qty - Usage
5. 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

[v-nuoc-msft]

Hi @ryanrizz 

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.

[ryanrizz]

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,

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

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