Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Austin Campbell]

Hello PBI Community,

I’ve been working on a seemingly easy but in reality a complex visual for awhile now. I feel like I’m very close to solving the solution but I’m at the point where I could use some help.

Basic Background:

• I am a Power BI Pro user
• I have a query that pulls current inventory levels
• I have a query that pulls expected sales forecast
• I have a query that pulls expected production forecast
• I have a query that pulls product hierarchy
• I have a date table that identifies production days from non-production days

My request is to build an actual/forecast table that (see Excel file for a detailed example):

1. If month is historical provide ACTUAL ENDING INVENTORY
   1. This is based off the Inventory query
2. If month is current or future provide a calculation
   1. Inventory = [Prior Month Inventory]-[Sales]+[Production]

Astute observers will recognize that my future calculation is an iterative or recursive calculation. DAX absolutely hates these types of circular dependencies and starts kicking up all sorts of errors. I have tried in vain dozens (if not hundreds by this point) different variations of DAX formulas to create the result but have always ended up in failure.

I recognize that a key component to solving this dilemma is based on the EARLIER equation similar to:

Sales Cumulative =
CALCULATE(
SUMX('SalesPlan'[SalesPlan]),
    FILTER(
         ALL('SalesPlan'[Date]),
         EARLIER('SalesPlan'[Date])>=[Date]
     )
)

I have experimented with creating a “virtual” table via the VAR/RETURN similar to the following:

Ending Inventory =
//LOAD MEASURES

VAR _actuals = SUM(Inventory[Inventory])

VAR _initial =

VAR _maxDate = CALCULATE(MAXX(Inventory,[DATE],REMOVEFILTERS(Inventory))

                RETURN

                CALCULATE(SUM(Inventory[Inventory],FILTER(Inventory,[Date]=_maxDate))

VAR _sales = SUM(SalesPlan[SalesPlan])

VAR _production = [AvgDailyProduction] * [ProdDaysInMonth]

//LOAD DATE VARIABLES

VAR _currentMonth = MONTH(TODAY())

VAR _startDate = FIRSTDATE(ALLSELECTED(Dates[Date]))

//TABLE CREATION
VAR _table1 = GENERATESERIES(_currentMonth,12)

VAR _table2 = ADDCOLUMNS(_table1, "Initial Inventory",_initial)

VAR _table3 = ADDCOLUMNS(_table2, "Date",DATE(YEAR(_startDate),[Value]+1,1)-1)

VAR _table4 = ADDCOLUMNS(_table3, "Production Cumulative", CALCULATE(_production, FILTER(_table3, EARLIER([Date])>=[Date])))

VAR _table5 = ADDCOLUMNS(_table4, "Sales Cumulative", CALCULATE(_sales, FILTER(_table4, EARLIER([Date])>=[Date])))

VAR _table6 = ADDCOLUMNS(_table5, "Shortfall Cumulative", [Sales Cumulative]-([Initial Inventory]+[Production Cumulative]))

VAR _table7 = ADDCOLUMNS(_table6, "Shortfall Cumalative Max So Far", MAX(MAXX(FILTER(_table6, [Date] <= [Date]), [Shortfall Cumulative]),0))

VAR _table8 = ADDCOLUMNS(_table7,"Closing Inventory",[Initial Inventory]+[Prouduction Cumulative]-[Sales Cumulative]+[Shortfall Cumalative Max So Far])

VAR _table9 = CROSSJOIN(_table8,Date)

RETURN

IF(SELECTEDVALUE(Date[Month Number])<_currentMonth,_actual,Maxx(_table9,[Closing Inventory]))

 FYI: this code resulted in providing actuals for historical months but generating 0.00 for future months

 I have included a sample excel file and sample .pbix file that provides a very basic example of my true file setup (most of my queries are derived from Oracle, SQL, and Excel).

 Also, as a point of record I’ve consulted the following webpages:

• http://blog.extrobe.co.uk/blog/2016/11/26/creating-virtual-tables-in-power-bi-using-dax/
• https://www.sqlbi.com/articles/from-sql-to-dax-projection/
• https://community.powerbi.com/t5/Community-Blog/Iteration-in-DAX/ba-p/1223311
• https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/637614
• https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314
• https://community.powerbi.com/t5/Community-Blog/Runge-Kutta-and-the-Limits-of-DAX/ba-p/357501
• https://community.powerbi.com/t5/Desktop/Recursive-Measure-PowerBI/m-p/637130
• https://community.powerbi.com/t5/Desktop/DAX-Create-a-measure-which-referencing-to-the-previously/td-p/204761
• https://gregdeckler.com/2019/03/06/previous-value-recursion-in-dax/
• https://stackoverflow.com/questions/61257536/how-to-perform-sum-of-previous-cells-of-same-column-in-powerbi
• https://www.edureka.co/community/86727/recursive-logic-in-dax
• https://www.youtube.com/watch?v=lyhS2txtZ44&t=3s
• https://www.sqlbi.com/articles/understanding-circular-dependencies/
• https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

——————————
Austin Campbell
Manufacturing Data Analyst
Mebane, NC
919-304-7886
——————————

[Marc Schroyen]

Hi Austin,

You don’t need iterative or recursive calculation.

You need to setup the right filter context.

First, you need to get last inventory by product:

Inventory Last Date = 
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxInventoryDates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Inventory, 'Product'[Item #] ),
            "@MaxInventoryDate", CALCULATE ( MAX ( Inventory[Date] ) )
        ),
        'Date'[Date] < MaxDate
    )
VAR MAxInventoryDateWithDataLineage =
    TREATAS ( MaxInventoryDates, 'Product'[Item #], 'Date'[Date] )
VAR Result =
    CALCULATE ( SUM ( Inventory[Inventory] ), MAxInventoryDateWithDataLineage )
RETURN
    Result

You can find the explanation here: https://www.daxpatterns.com/semi-additive-calculations/
Once done, you have to calculate the forecast

Ending Inventory = 
VAR MaxInventoryDate = CALCULATE( MAX(Inventory[Date]), REMOVEFILTERS(Inventory))
VAR MaxProductionDate = MAX(Production[Date] )
VAR MaxSalesDate = MAX(SalesPlan[Date])
VAR _actuals = SUM(Inventory[Inventory])
VAR _sales = 
    CALCULATE(
    SUM( SalesPlan[SalesPlan] ),
    FILTER( ALL('Date'), 'Date'[Date] <= MAxSalesDate && 'Date'[Date] > MaxInventoryDate )
    )
VAR _production = 
    CALCULATE(
        SUM(Production[Production]),
        FILTER(ALL('Date'),'Date'[Date] <= MaxProductionDate && 'Date'[Date] > MaxInventoryDate )
    )
VAR Forecast = Inventory[Inventory Last Date] + _production - _sales
RETURN
SWITCH(
    TRUE(),
    MAX( 'Date'[Date]) <= MaxInventoryDate, _actuals,
    Forecast
)

Basically, you start everytime from the last inventory date and add production – sales up to the current month.

Alternative for forecast:

Ending Inventory = 
VAR MaxInventoryDate = CALCULATE( MAX(Inventory[Date]), REMOVEFILTERS(Inventory))
VAR MaxProductionDate = MAX(Production[Date] )
VAR MaxSalesDate = MAX(SalesPlan[Date])
VAR _actuals = SUM(Inventory[Inventory])
VAR _sales = 
    CALCULATE(
    SUM( SalesPlan[SalesPlan] ),
    'Date'[Date] <= MAxSalesDate,
    'Date'[Date] > MaxInventoryDate 
    )
/*VAR _production = 
    CALCULATE(
        SUM(Production[Production]),
        FILTER(ALL('Date'),'Date'[Date] <= MaxProductionDate && 'Date'[Date] > MaxInventoryDate )
    ) */
VAR _production = 
    CALCULATE(
        SUM(Production[Production]),
        'Date'[Date] <= MaxProductionDate,
        'Date'[Date] > MaxInventoryDate 
    )
VAR Forecast = Inventory[Inventory Last Date] + _production - _sales
RETURN
SWITCH(
    TRUE(),
    MAX( 'Date'[Date]) <= MaxInventoryDate, _actuals,
    Forecast
)

——————————
Marc Schroyen
Liège Belgium
——————————
——————————————-

[Austin Campbell]

 Thank you so much. These calculations solved my issue. I cannot express my gratitude enough!?

——————————
Austin Campbell
Manufacturing Data Analyst
Mebane NC
9193047886
——————————
——————————————-