cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Responsive Resident

## Excess Stock by date

Hi All,

I am trying to do Excess Stock analysis in Power BI  but can't get this work.

Please see below for example and Result:

DEMAND DATA:

STOCK DATA:

RESULT:

Thanks in advance if you can help to achieve above result.

1 ACCEPTED SOLUTION
Community Support

Hi @mb0307 ,

``````"Total Available Stock Meeting Demand" = CALCULATE(SUM(STOCK[Stock]),FILTER(ALL(STOCK),STOCK[Available Date].[Month]=SELECTEDVALUE(DEMAND[Date of Demand].[Month])&&STOCK[Available Date]>=SELECTEDVALUE(DEMAND[Date of Demand])))

Stock NOT Meeting Demand = CALCULATE(SUM(STOCK[Stock]),FILTER(ALL(STOCK),STOCK[Available Date].[Month]=SELECTEDVALUE(DEMAND[Date of Demand].[Month])&&STOCK[Available Date]<SELECTEDVALUE(DEMAND[Date of Demand])))

Excess Stock = [Stock NOT Meeting Demand]-SUM(DEMAND[Demand])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
4 REPLIES 4
Community Support

Hi @mb0307 ,

``````"Total Available Stock Meeting Demand" = CALCULATE(SUM(STOCK[Stock]),FILTER(ALL(STOCK),STOCK[Available Date].[Month]=SELECTEDVALUE(DEMAND[Date of Demand].[Month])&&STOCK[Available Date]>=SELECTEDVALUE(DEMAND[Date of Demand])))

Stock NOT Meeting Demand = CALCULATE(SUM(STOCK[Stock]),FILTER(ALL(STOCK),STOCK[Available Date].[Month]=SELECTEDVALUE(DEMAND[Date of Demand].[Month])&&STOCK[Available Date]<SELECTEDVALUE(DEMAND[Date of Demand])))

Excess Stock = [Stock NOT Meeting Demand]-SUM(DEMAND[Demand])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Solution Specialist

Can we see your data ?

if you have this 2 tables you need to merge your tables on product and month/year

Responsive Resident

Please share PBIX if you manage to it in power bi.

Thanks

Solution Specialist

https://file.io/jejO1WwQd8to