How to create a DAX measure to calculate inventory

[Saurabh15]

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.

[tamerj1]

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] )
    )

[Saurabh15]

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).

Could you please help in resolving this?

Thanks,

Saurabh

[tamerj1]

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

[Saurabh15]

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

[Anonymous]

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

[Saurabh15]

Hi @Anonymous ,

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

[Anonymous]

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

[Saurabh15]

Hi @Anonymous ,

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

[Anonymous]

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.

[Saurabh15]

Hi @Anonymous ,

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.

Please help me with this issue.

Thanks,

Saurabh

[tmack99]

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.