cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunofs123
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
v-piga-msft
Resident Rockstar
Resident Rockstar

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?

Capture.PNG

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.

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:

 


Capturar1.PNG

 

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:
 
Capturar2.PNG
 
****
 
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: 
 
Capturar3.PNG
 
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!
 
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.