Inventory risk based on expiry dates

Good morning, I will log the ticket and post the results here to keep everything together, once it has been resolved I will mark solution as Accept

Hi GGerritsen ,

The measure is getting error which is Mdx error in excel, you can try this write this in excel

CalculatedColumn = 
VAR RiskAmount = [Forecast] * [Months left Inventory]
VAR SumForecast = CALCULATE(SUM(TableName[Forecast]), TableName[Item No.] = EARLIER(TableName[Item No.]))
VAR SumRisk = CALCULATE(SUM(TableName[Risk]), TableName[Item No.] = EARLIER(TableName[Item No.]))

RETURN 
   IF (
       [Expiry date] < TODAY(),
       [Forecast],
       IF (
           SumForecast > RiskAmount,
           SumForecast - SumRisk - RiskAmount,
           0
       )
   )

And also use switch function instead of nested if for the below one:

RETURN
SWITCH(
   TRUE(),
   CurrentMonth < FirstExpiry, "Before First Expiry",
   CurrentMonth >= FirstExpiry && CurrentMonth < SecondExpiry, "Between First and Second Expiry",
   CurrentMonth >= SecondExpiry && CurrentMonth < ThirdExpiry, "Between Second and Third Expiry",
   CurrentMonth >= ThirdExpiry, "After Third Expiry",
   "Unknown"

I think by changing these may help to run them faster.

Thank you and Regards,
Menaka Kota.

Thank you for your help so far, but I think I have made too many changes thst nothing is working anylonger.  I am starting over,  hoping this help

I have 2 files.

Forecast data has weekly expected sales

Inventory data has Qty and expiry dates.

(In "real life"  files have mulitple items / expiry dates but for this I want to keep it symple)

I need to run a report that shows what my risk is for the items by the time it reached the expiry date.

example,  Expiry date August 04 2025,  I will sell untill this date,  there will be x cases at risk.

After August 04, I will start selling the next lot (September 02)  there will be x cases at risk.

and only after September 02 (or if I sell the lot that expires Sept 02 => whichever comes first) I wills start selling the lot that expires November 05.

Currently doing this in Excel, but based on the number of items / lots this is getting cumbersome, thinking BI is a better option, and I would be able to share this information better with the sales group to ensure they are pushing the proudcts that need to sell first.

Open to sugestions.

G.

Hi GGerritsen ,

Once try these 
cumulative forecast measure:

Cumulative Fcst :=
CALCULATE(
SUM(Forecast[Fcst]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
Create a calculated table to simulate forecast consumption by expiry:
InventoryRisk =
VAR LotTable =
ADDCOLUMNS(
Inventory,
"FcstUntilExpiry",
CALCULATE(
SUM(Forecast[Fcst]),
FILTER(
Forecast,
Forecast[Item] = EARLIER(Inventory[Item]) &&
Forecast[Date] <= Inventory[Expiry Date]
)
)
)
RETURN
ADDCOLUMNS(
LotTable,
"RiskQty", [Qty] - [FcstUntilExpiry]
)

Getting stuck at the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Hi GGerritsen ,

Your DAX will return a table with multi columns, it couldn't be a measure.

Therefore you need to use this expression on (Create new table)

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Hi GGerritsen  ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

hello, sorry, Friday was a travel day,  was out of office and working from a different plant last week, just catching up on everything and will work at this again during the week.

G.

Moved it into it's own table,  here are the results

for the lot expiring August 04,  current inventory 6,174 units

Cumulative forecast between now and August 04 3,329 units

My expected result would have been risk of 6174-3329 = 2845 units

The second lot 2142 units

forecast between Augsut 04 anbd Sept 02  =  3,994 - 3,329  = 665 units

Risk for second lot  is  2,142 units -  665 sales  =  1,477 units risk.

I'm sure I am doing something wrong that is giving me the different numbers,  just can't find where it goes off.

G.

Hi GGerritsen ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.