Excess Stock by date

[mb0307]

Hi All,

Download Sample Data - Excel 

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.

[v-jayw-msft · Accepted solution]

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

[freginier]

Can we see your data ? 

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

[mb0307]

Here a link to data:

Data Download - Excel 

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

Thanks  

[freginier]

See this I create the query, I need more information about business rules to create total's 

https://file.io/jejO1WwQd8to