Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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):
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
and this one is with filters, having the same values pf Sales Forecast
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]))
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 @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
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 @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
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.
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 |
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
13 | |
12 | |
9 |