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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Saurabh15
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.

 

Saurabh15_0-1670698630429.png

 

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
tamerj1
Super User
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)

1.png

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

 

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

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

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

Saurabh15_0-1671120390394.png

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

Saurabh15_1-1671120538935.png

Thanks,

Saurabh

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

vpollymsft_0-1670912697900.png

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

Hi @v-rongtiep-msft ,

 

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

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

Hi @v-rongtiep-msft ,

 

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

v-rongtiep-msft
Community Support
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]

vpollymsft_0-1670823217176.png

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.

Hi @v-rongtiep-msft ,

 

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-YearShipmentsInventory ActualSales ActualSales Actual/ForecastInventory Actual/Forecast
Jul-224052707052
Aug-22801052727105
Sep-22421444343144
Oct-221001549090154
Nov-22103132125125132
Dec-22711544949154
Jan-2380  88146
Feb-2376  88134
Mar-2343  8889
Apr-2360  8861
May-2333  886
Jun-23110  8828

 

 

  • 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
Advocate II
Advocate II

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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