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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rahulsingh
Helper I
Helper I

Previous Balance Calculation Issue

Hi Everyone, 

 

I ran into an interesting issue where I need previous balance from last reported month but PrevoiusBalance DAX won't work because Date reported are not in continuous order. How can I get this to work, Please see screenshot, Any help would be greatly appreciated.PrevoiusBalance.JPG

 

2 ACCEPTED SOLUTIONS
v-danhe-msft
Employee
Employee

Hi @Rahulsingh,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Create two measures:

Index = RANKX(ALL(Table1),FIRSTNONBLANK('Table1'[Dates],'Table1'[Dates]),,ASC,Dense)
Measure = var a=[Index]-1
return CALCULATE(SUM(Table1[Balance]),FILTER(ALL('Table1'),'Table1'[Index]=a))

Result:

3.PNG

 You could also download the pbix file to have a view:

https://www.dropbox.com/s/vgkl8c3yf5vccn0/Previous%20Balance%20Calculation%20Issue.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

 Thank you @v-danhe-msft it worked. Thanks a lot 

View solution in original post

6 REPLIES 6
v-danhe-msft
Employee
Employee

Hi @Rahulsingh,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Create two measures:

Index = RANKX(ALL(Table1),FIRSTNONBLANK('Table1'[Dates],'Table1'[Dates]),,ASC,Dense)
Measure = var a=[Index]-1
return CALCULATE(SUM(Table1[Balance]),FILTER(ALL('Table1'),'Table1'[Index]=a))

Result:

3.PNG

 You could also download the pbix file to have a view:

https://www.dropbox.com/s/vgkl8c3yf5vccn0/Previous%20Balance%20Calculation%20Issue.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 Thank you @v-danhe-msft it worked. Thanks a lot 

Ashish_Mathur
Super User
Super User

Hi,

 

Share the link from where i can download your PBI file.  Please also show the expected result there.


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

I'd suggest using an index column in Power Query, then writing a DAX formula measure to get the value of the Index minus 1.

 

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

Last Value = var indexValue = MAX('YourTable'[Index])
RETURN
IF(
	indexValue > 1,
	CALCULATE(
		SUM('YourTable'[Balance]),
		ALL('YourTable'),
		'YourTable'[Index] = (indexValue - 1)
	),
	0
)

 

Hi @Anonymous

what does INDEX > 1 in your formula stand? When I use INDEX > 1 it's giving me an error 

Anonymous
Not applicable

@Rahulsingh that should have been indexValue  it represents an error trap for the occasion of where this calcules for the first row in the data (index = 1).  That was just a silly typo on my part.  I've updated the code.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.