cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## How to create a DAX measure to calculate inventory

Hi all,

I am struggling to create a measure to calculate the current month inventory in my power BI report.

Below is the detailed explaination of the problem.

I have the measure for Sales Forecast ready which is average of past 3 months sales (Sales Actual) and is constant for all the future months. Eg., Sales Forecast for Jan-23 and after = Average(Oct'+Nov + Dec)

But struggling to create a measure for Inventory Forecast.

Inventory forecast (current month)= Inventory Forecast (previous month) + Shipments (current month) - Sales Forecast (current month)

Eg., Inventory Forecast for Feb'23 = Inventory Forecast (Jan'23) + Shipments (Feb'23) - Sales Forecast (Feb''23)

If anyone can solve this then it'd be a big help for me.

Thanks.

11 REPLIES 11
Super User

Hi @Saurabh15
Please refer to attached sample file wit the solution. The solution is based on some assumptions (however, can be adjusted to match your actual situation in case is different):

1. Month-Year column is from a connected 'Date' table.
2. [Shipments], [Inventory Actual], [Sales Actual] and [Sales Forecast] are all measures.
3. An integer data type {YearMonth] column do exist, or should be added (can be easily added as per sample file)

``````Inventory Forecast =
VAR CurreNtYearMonth = SELECTEDVALUE ( 'Date'[YearMonth] )
VAR T1 =
SUMMARIZE (
ALL ( 'Table' ),
'Date'[YearMonth],
"@Shipments", [Shipments],
"@Sales", [Sales Actual],
"@Inventory", [Inventory Actual],
"@SalesForcast", [Sales Forecast]
)
VAR T2 = TOPN ( 1, FILTER ( T1, [@Sales] > 0 ), [YearMonth] )
VAR LastInventory = MAXX ( T2, [@Inventory] )
VAR LastYeaMonthWithSales = MAXX ( T2, [YearMonth] )
VAR T3 = FILTER ( T1, [@Sales] = BLANK ( ) && [YearMonth] <= CurreNtYearMonth )
RETURN
IF (
CurreNtYearMonth > LastYeaMonthWithSales,
LastInventory + SUMX ( T3, [@Shipments] - [@SalesForcast] )
)``````

Frequent Visitor

Hi @tamerj1 ,

Thanks for the support. This works but not as per the filter context.

In my case the data is for multiple shops and products, but the inventory forecast and sales forecast values are calculated on the whole data (irrespective of the shops and products selected in the filters).

Thanks,

Saurabh

Super User

@Saurabh15
Sure but you need to create a sample file with the right context to work with. Thank you.

Frequent Visitor

Hi @tamerj1 ,

I have tried to follow your solution with the sample data, but unable to get the correct Sales Forecast values according to the filter context.

Also, the Inventory Forecast values are blank.

Below is the snip without filters

and this one is with filters, having the same values pf Sales Forecast

Thanks,

Saurabh

Community Support

Hi @Saurabh15 ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create measures.

``````inver = var _month=EDATE(MAX('Table'[month year]),-1)
var _1=CALCULATE(MAX('Table'[inventory forecast]),FILTER(ALL('Table'),'Table'[month year]=_month))
var _re=IF(MAX('Table'[inventory forecast])=BLANK(),MAX('Table'[inventory]),MAX('Table'[inventory forecast]))
return _re``````
``````Measure 2 = var _1=EDATE(MAX('Table'[month year]),-1)
var _2=
CALCULATE([inver],FILTER(ALL('Table'),'Table'[month year]=_1))
var _1re=
MAX('Table'[shipments])+_2-[Sales Forecast]
return
IF(MAX('Table'[inventory])=BLANK(),_1re,MAX('Table'[inventory]))``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) . Let me know the measures in your pbix file.

Best Regards
Community Support Team _ Polly

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

Frequent Visitor

All the Measure 2 values given by your solution are correct and that is what I need. But to calculate it you have used Inventory Forecast which is actualy not there is my table.

Can you calculate Measure 2 without using Inventory Forecast?

Thanks,

Saurabh

Community Support

Hi @Saurabh15 ,

If you need to calculate the value of the previous row when calculating this row, the calculation in DAX is very complicated. I recommend that you calculate the Inventory Forecast column first.

Best Regards
Community Support Team _ Polly

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

Frequent Visitor

Sure, but how to get the values of Inventory Forecast

In your pbix file I can see that Inventory Forecast column is already present in the table, which is not there in my data.

Thanks,

Saurabh

Community Support

Hi @Saurabh15 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create measures.

``````Measure = var _1=EDATE(MAX('Table'[month year]),-3)
var _2=
CALCULATE(AVERAGE('Table'[sales actual]),FILTER(ALL('Table'),'Table'[month year]>=_1&&'Table'[month year]<=SELECTEDVALUE('Table'[month year])))
var _month=MONTH(MAX('Table'[month year]))
var _year=YEAR(MAX('Table'[month year]))
return
IF(_month=1&&_year=YEAR(SELECTEDVALUE('Table'[month year])),_2,BLANK())``````
``````Sales Forecast = var _1=MAXX(ALL('Table'),[Measure])
var _2=CALCULATE(MAX('Table'[month year]),FILTER(ALL('Table'),[Measure]<>BLANK()))
return
IF(MAX('Table'[month year])>=_2,_1,BLANK())``````
``````Inventory forecast1 = var _month=EDATE(MAX('Table'[month year]),-1)
var _1=CALCULATE(MAX('Table'[inventory forecast]),FILTER(ALL('Table'),'Table'[month year]=_month))
return
_1+MAX('Table'[shipments])-[Sales Forecast]``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Frequent Visitor

The measures that you have created to calculate the Sales Forecast are correct, but the Inventory Forecast calculations are not how they should. Let me explain it further.

 Month-Year Shipments Inventory Actual Sales Actual Sales Actual/Forecast Inventory Actual/Forecast Jul-22 40 52 70 70 52 Aug-22 80 105 27 27 105 Sep-22 42 144 43 43 144 Oct-22 100 154 90 90 154 Nov-22 103 132 125 125 132 Dec-22 71 154 49 49 154 Jan-23 80 88 146 Feb-23 76 88 134 Mar-23 43 88 89 Apr-23 60 88 61 May-23 33 88 6 Jun-23 110 88 28

• I have all the values of Shipments - No measure needed here.
• I have actual values of Sales - A measure ready to forecast the future values = Average of past 3 values
• I have actual values of Inventory as well - Need a measure to calculate Future Inventory values, where

Inventory Actual/Forecast = if (date<=today, Inventory Actual, Inventory Actual/Forecast (previous month) + Shipments (current month) - Sales Actual/Forecast (current month))

When I use this measure, Power BI throws the circular reference error, and I an unable to use the previous month value of the same measure to calculate its current month value.

Thanks,

Saurabh

To calculate the current month inventory in Power BI, you will need to use a combination of the SUM and CALCULATE functions. The SUM function can be used to calculate the total inventory for a given date range, and the CALCULATE function can be used to dynamically filter the date range to only include the current month.

Here is an example of how you might create a measure to calculate the current month inventory:

Current Month Inventory =
CALCULATE(
SUM(Inventory[Quantity]),
DATESINPERIOD(Inventory[Date], LASTDATE(Inventory[Date]), -1, MONTH)
)

This measure uses the DATESINPERIOD function to generate a table of dates that includes all of the dates in the current month. It then uses the CALCULATE function to filter the SUM(Inventory[Quantity]) measure to only include the dates in the current month. This will give you the total inventory for the current month.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors