Previous Balance Calculation Issue

[Rahulsingh]

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.

[v-danhe-msft · Accepted solution]

Hi @Rahulsingh,

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

Sample data:

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:

 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

[v-danhe-msft · Accepted solution]

Hi @Rahulsingh,

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

Sample data:

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:

 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

[Rahulsingh · Accepted solution]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

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
)

[Rahulsingh]

Hi @Anonymous

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

[Anonymous]

@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.