Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Godfrey
New Member

Return Last Known Balance for each Account

Hi!

 

I have the following data:

 

Table name: Reconciliation

Date IDAccount ID  Balance  
20240101  A100
20240201A200
20240301B300

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  
A100200200
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  
A100200*
B  300

 

The Problem

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

2 ACCEPTED SOLUTIONS

It's all covered in this article 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1712453965693.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1712453965693.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It's all covered in this article 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.