Return Last Known Balance for each Account

[Godfrey]

Hi!

I have the following data:

Table name: Reconciliation

Date ID	Account ID	Balance
20240101	A	100
20240201	A	200
20240301	B	300

I also have a date table connected to the Date ID
I want the function to return the last balance for the last date relative to the filtered month.

What I want

In the end I want create a matrix visual that with Dates[YYYYMM] as columns, Reconciliation[Account ID] as rows and the newly created Measure "Last Balace" as values :

	202401	202402	202403
A	100	200	200
B			300

What I've tried
I've tried this formula but it leaves the last A account Blank.

LastBalance = 
CALCULATE(SUM(Reconciliation[Balance]),
Reconciliation[Date ID] = MAX(Reconciliation[Date ID]))

	202401	202402	202403
A	100	200	*
B			300

The Problem

This leaves the last month for the A account Empty*
Any help would be much appreciated!

[MattAllington · Accepted solution]

It's all covered in this article 

https://www.daxpatterns.com/semi-additive-calculations/

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[MattAllington · Accepted solution]

It's all covered in this article 

https://www.daxpatterns.com/semi-additive-calculations/