SUM LASTDATE FOR EACH ELEMENT CONTEXT

[u92690]

Hi all, 

I'm struggling to find  the correct answer. I have a sheet with bank balances aggregated. What i want to do is:

"For each bank, look me up the last day within the month, and save that balance". So far what i've tried is:

Balance_end = CALCULATE([balance_sum],LASTDATE('Test last saldo'[Date])) 

which basically looks up the last day for each month in all the table and then sum all tha balances in that day, and does not work properly. Some how in need to introduce the context of the calculation, which is a bank level, to collect in tha sum all the balances, whatever it is the lastday for each one.

I attach the table example for reference.

file link

Thank you

[v-jingzhang · Accepted solution]

Hi @u92690 

If you want a measure, you could try this one

Last Balance Total = 
VAR _table = SUMMARIZE('Table','Table'[Bank],"Last_Date",MAX('Table'[Date]))
VAR _table2 = ADDCOLUMNS(_table,"Last Balance",SUMX(FILTER('Table','Table'[Bank]=[Bank]&&'Table'[Date]=[Last_Date]),'Table'[Balance]))
RETURN
SUMX(_table2,[Last Balance])

Add a calendar table to your model and create a relationship between it and balance table on date columns. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[u92690]

I'm sorry but i don't think this solution fits my needs. I want to sum the last balance of the month for each bank, but i could have one bank whose last balance is the 01/25/2021 and another one is 01/31/2021. Therefore, I can't use the last date in the whole database for january, but the formula needs to catch the last balance for that bank no matter what the day is.

[amitchandak]

@u92690 , Based on what I got

closingbalancemonth(Sum('Table'[Value]), [balance_sum])

or

CALCULATE(Lastnonblankvalue('Test last saldo'[Date] , [balance_sum]) , filter(all('Date') , 'Date'[Month Year] = max('Date'[Month Year] )))

refer if needed

https://www.youtube.com/watch?v=yPQ9UV37LOU