Help with final balance (stock calculation)

[brunofs123]

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!!!

[v-piga-msft]

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]

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!