First non null value

Question

I have data that is sorted by report month. Here is a screen shot for one Customer ID. Each Customer can have multiple accounts, but here is one account example:

What I need to extract is the last $ value (current balance) before the customer closed their account. In this example, it would be $xxx,xxx on report month 04/30/2022. I have created a duplicate current balance column with null values to replace the $0 so that I could do a DAX which would be something like "The first non null value"

I can't use FIRSTNONBLANK as it could be the case that the last $ in the account was not necessarily their highest/lowest $.

Is there an easy way to get this?
Thank you!

johnt75 · Answer

I think you can use
Last Balance =
LASTNONBLANKVALUE ( 'Table'[Report Month], SUM ( 'Table'[Duplicate balance] ) )

kw123 · Answer

johnt75&nbsp;Thanks! Unfortunately that returns blanks&nbsp;

kw123 · Answer

johnt75&nbsp;I have tried using this one:&nbsp;Last Balance = var i = SELECTEDVALUE('Query1'[MemberNumber])var d2 = CALCULATE(LASTNONBLANKVALUE('Query1'[Duplicate current balance],SELECTEDVALUE('Query1'[Duplicate current balance])),all('Query1'),'Query1'[Duplicate current balance] &lt;&gt;BLANK(),'Query1'[MemberNumber]=i)return d2But it returns the largest $ amount in the customers account history, instead of whatever the last $ amount was before they closed their account.&nbsp;The DAX you suggested returned blank on the report.&nbsp; As a column, it added up the entirety of the account balances.&nbsp; I switched the [reportmonth] to closeddate and that didn't seem to work either (just returned blank)&nbsp;

anonymous · Answer

Hi&nbsp;KW123&nbsp;,
In order to get a better understanding on your requirement and give you a suitable solution, please provide some&nbsp;fake data&nbsp;in your table 'Query1' (exclude&nbsp;sensitive&nbsp;data) with&nbsp;Text&nbsp;format and your&nbsp;expected result&nbsp;with backend logic and special examples.&nbsp;It is better&nbsp;if you can share a&nbsp;simplified&nbsp;pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards

kw123 · Answer

Anonymous&nbsp;Thank you for the feedback.&nbsp; I hope the below helps:&nbsp;This is how the data is in the report.&nbsp; This is for one customer ID.&nbsp; Some customer IDs have multiple account numbers, but in this case it only has one.&nbsp; In my report, I will be making it for every customer ID we have with a closed account.&nbsp;&nbsp;I am trying to make a report which shows the last balance in the customers account before they closed their account (and then the current balance changes to $0) So for this customer, I want to extract the $ balance that shows in that row on 04-30-2022.&nbsp; Where I am running into problems is that the last balance isn't necessarily the largest $ amount in their account history.&nbsp; So using a MAX or LASTNONBLANK I don't think will work.&nbsp;Here is an example:&nbsp;&nbsp;Customer ID&nbsp;Customer&nbsp; &nbsp;Account&nbsp;Open Date&nbsp;Closed Date&nbsp;Current&nbsp; &nbsp;Balance&nbsp;Last Balance&nbsp; &nbsp;in account&nbsp; &nbsp;(column I am&nbsp; &nbsp;trying to&nbsp; &nbsp;caluclate)112311/07/2019&nbsp;04/30/2022$0$500245606/20/2020&nbsp;01/03/2022$0$1000378906/04/2017&nbsp;05/09/2020$0$200423403/08/2021&nbsp;09/04/2022$0$300534507/06/2020&nbsp;11/02/2021$0$700&nbsp;Assume that customer ID 1 is the same customer in the above screenshot of the Data.&nbsp; The first $ value that is in the black box, their closed $ amount, is $500 on 04/30/2022.&nbsp; Since the account is closed, the balance becomes $0 for the report months thereafter.&nbsp;There is a direct relationship between the dates table and the report month and an indirect with Closed date and date.&nbsp;Let me know if you require more information. Thanks again for your help&nbsp;&nbsp;

Forum Discussion

First non null value

7 Replies

Related content

Handling Null Values

Counting nulls

Null value error

Replace Values Null to Blank or Blank to Null

Replace Errors values with Null values