Forum Discussion

brunofs123's avatar
brunofs123
Frequent Visitor
7 years ago

Help with final balance (stock calculation)

Hello fellow pbi users!

 

My dataset is here 

 

I need to calculate dynamically the qty stock left in my inventory and $ total amount of this inventory left :

units remaning of that product * unit price of it  (which is the last unit price of that last SALE).

 

 

I have a calendar table connected with a fact table

 

 

I wanna show it in a table, dynamically changed by DATES.

 

Problems:

 

1) the total is wrong

 

 

 

The "correct" $ total would be $44.810

 

 

2) I'm struggling to find the "remaning" measure correct. For instance, if you select dates from 21-04-2019, the remaining for POSMIL product will be NEGATIVE. 

I know why is wrong, just dont know how to fix it =)

 

I've tried with an calculated column using EARLIER approach, but didnt work out. I've 

 

 

I was able to get to the result with a SUMMARIZECOLUMN new table, but i didnt like that approach:

Remaning CC wrong = SUMX(FILTER(Total;Total[DATE]<=EARLIER(Total[DATE]));Total[QTY BOUGHT] - Total[QTY SOLD])

 

 

Thanks!!!

 

 

 

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi brunofs123 ,

    I still have a little confused about your scenario.

    For your first problem, I cannot understand that why the correct total is 44.810, could you explain it?

    In addition, for your sceond problem, it seems that the remaining for POSMIL product should be -100 based on your data sample. What is your desired output?

    Best  Regards,

    Cherry

    • brunofs123's avatar
      brunofs123
      Frequent Visitor
      7 years ago

      Hi there!

       

      1) For your first problem, I cannot understand that why the correct total is 44.810, could you explain it?

       

      With no filter dates applied, i would have:

       


       

      My inventory balance [$ IN STOCK LEFT]  should be the price bought for the item * units i have LEFT of that item

       

      I think in my measure it was incorrect. I've changed like below:

       

      unit price last BUY = CALCULATE(MAX(Total[UNITY VALUE]);Total[TIPO]= "BUY";
      FILTER (
      ALLSELECTED( 'Calendario'[Date] );
      'Calendario'[Date] <= MAX( Calendario[Date] )
      ))
       
      But it still results in not expected value:
       
       
      ****
       
      2)  it seems that the remaining for POSMIL product should be -100 based on your data sample. What is your desired output?
       
      Should be 300: 
       
       
      Bought: 300 on 09-04-19
      Sold: 100 only on 25-04-19, so my balance is still 300 on 21-04-2019.
       
       
      I kinda feel dumb right now because the measure it's poorly written. I should have make a accumulated sum. 
       
       
      Thanks for your patience!