# Last balance by product

Let’s say I have a balance by product and date. But I do not have the balance for every date.
For eg, 1/1/2020 - apples - 100, 1/5/2020 - oranges 250, 1/8/2020 apples 50

Now I want to create a measure such that it will look up the balance as of the last date.
apples oranges Total
1/1/2020 100 - 100
1/2/2020 100 - 100
1/3/2020 100 - 100
1/4/2020 100 - 100
1/5/2020 100 250 350
1/6/2020 100 250 350
1/7/2020 100 250 350
1/8/2020 50 250 350

I did something like this - and I got the individual dates - but the total is not working…
Last Balance =

• var dt = Calculate(LastDate(Balances[Date]), Filter(All(‘Calendar’[Date]), ‘Calendar’[Date] <= Max(‘Calendar’[Date])))*
• return Calculate(Max(Balances[Balance]), Filter(All(‘Calendar’[Date]), ‘Calendar’[Date] = dt))*

This gives me the following result
apples oranges Total
1/1/2020 100 - 100
1/2/2020 100 - 100
1/3/2020 100 - 100
1/4/2020 100 - 100
1/5/2020 100 250 250
1/6/2020 100 250 250
1/7/2020 100 250 250
1/8/2020 50 250 50
How do you make this work - So the total would work as well?

Thank you

Hi @speters,

Welcome to the forum!

Could you provide a small sample PBIX with some dummy data, so members can better assist you?
Thanks.

Hi Susan,

Most of the issues I’ve seen where the totals aren’t making sense is due to the CALCULATE function changing the context of the measure behind the scenes and giving unexpected totals. Here’s a video with Sam covering wonky totals and how to prevent them.

I hope this helps.

the measure should say balance as of the last date as of the given product. In the product total, it is working good because there is only one product. But in the total of all the products, it is failing because it is getting the last date of all the products…whereas I want it to go to add the balance as of the last date of each product. I am not sure how to do this.

Hi @speters,

So I think I understand your requirement - can you see if this pattern does the trick for you?
It generates a virtual table with the last balance for all products and then sums them up.

``````LastBalance =
VAR myTable =