topic First non null value in DAX Commands and Tips

First non null value

KW123 — Thu, 17 Nov 2022 04:21:25 GMT

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!

Re: First non null value

johnt75 — Thu, 17 Nov 2022 10:48:46 GMT

I think you can use

Last Balance = LASTNONBLANKVALUE ( 'Table'[Report Month], SUM ( 'Table'[Duplicate balance] ) )

Re: First non null value

KW123 — Thu, 17 Nov 2022 17:45:05 GMT

johnt75 Thanks! Unfortunately that returns blanks

Re: First non null value

KW123 — Fri, 18 Nov 2022 00:03:54 GMT

johnt75

I have tried using this one:

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] <>BLANK(),'Query1'[MemberNumber]=i)

return d2

But it returns the largest $ amount in the customers account history, instead of whatever the last $ amount was before they closed their account.

The DAX you suggested returned blank on the report. As a column, it added up the entirety of the account balances. I switched the [reportmonth] to closeddate and that didn't seem to work either (just returned blank)

Re: First non null value

Anonymous — Fri, 18 Nov 2022 10:05:46 GMT

Hi KW123 ,

In order to get a better understanding on your requirement and give you a suitable solution, please provide some fake data in your table 'Query1' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified 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

Re: First non null value

KW123 — Fri, 18 Nov 2022 17:41:34 GMT

Anonymous
Thank you for the feedback. I hope the below helps:

This is how the data is in the report. This is for one customer ID. Some customer IDs have multiple account numbers, but in this case it only has one. In my report, I will be making it for every customer ID we have with a closed account.

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. Where I am running into problems is that the last balance isn't necessarily the largest $ amount in their account history. So using a MAX or LASTNONBLANK I don't think will work.

Here is an example:

Customer ID	Customer Account	Open Date	Closed Date	Current Balance	Last Balance in account (column I am trying to caluclate)
1	123	11/07/2019	04/30/2022	$0	$500
2	456	06/20/2020	01/03/2022	$0	$1000
3	789	06/04/2017	05/09/2020	$0	$200
4	234	03/08/2021	09/04/2022	$0	$300
5	345	07/06/2020	11/02/2021	$0	$700

Assume that customer ID 1 is the same customer in the above screenshot of the Data. The first $ value that is in the black box, their closed $ amount, is $500 on 04/30/2022. Since the account is closed, the balance becomes $0 for the report months thereafter.

There is a direct relationship between the dates table and the report month and an indirect with Closed date and date.

Let me know if you require more information. Thanks again for your help

Re: First non null value

KW123 — Mon, 21 Nov 2022 22:25:13 GMT

Bumping this up....

Re: First non null value

Anonymous — Fri, 25 Nov 2022 10:10:38 GMT

Hi KW123 ,

I'm sorry that I still can't understanding your requirement. Do you want to get the last balance which is not 0 for per customer per account? For example, if you have the data in below table, what's your expected result? What you want to get is 800? Could you please share a simplified pbix file(exclude sensitive info) with some sample data and backend logic? You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Customer ID	Customer Account	Open Date	Closed Date	xxDate	Current Balance
1	123	11/7/2019	04/30/2022	3/2/2021	$4,500
1	123	11/8/2019	04/30/2023	12/14/2021	3200
1	123	11/9/2019	04/30/2024	2/28/2022	1500
1	123	11/10/2019	04/30/2025	4/3/2022	800
1	123	11/10/2019	04/30/2025	4/30/2022	0

Best Regards