Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ryanrizz
Frequent Visitor

Inventory Forecast Future

Hi All

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

 

this calculation in Excel

ryanrizz_1-1708880516088.png

 

  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"
 

ryanrizz_2-1708880927816.png

 

Thank you in advance

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

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. 

 

vnuocmsft_0-1708916630907.png

 

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.

 

vnuocmsft_1-1708916684808.png

 

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.

 

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

ryanrizz_0-1709043055992.png

 



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors