cancel
Showing results for
Did you mean:
Frequent Visitor

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

2 REPLIES 2
Resident Rockstar

Hi @brunofs123 ,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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:

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.

Announcements