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