Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
4 years ago
Solved

Excess Stock by date

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • freginier's avatar
    freginier
    Solution Sage
    4 years ago

    Can we see your data ? 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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