Last balance by product

DAX DAX Calculations
1

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

2

Hi @speters,

Welcome to the forum!

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

3

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.

4

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.

5

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 =
ADDCOLUMNS(
    ADDCOLUMNS(
        VALUES( Products[Fruit] ),
        "LastDate", CALCULATE( MAX( Balances[Date] ), ALLSELECTED( Dates ))
    ), "Stock", CALCULATE( SUM( Balances[Balance] ), KEEPFILTERS( FILTER( Balances, Balances[Date] = [LastDate] )))
)
RETURN

SUMX( myTable, [Stock] )
6

Hi @speters, we’ve noticed that no response has been received from you since 12th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!