Forum Discussion

KW123's avatar
KW123
Helper V
3 years ago

First non null value

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...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

KW123's avatar
KW123
Helper V
3 years ago

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)
112311/07/2019 04/30/2022$0$500
245606/20/2020 01/03/2022$0$1000
378906/04/2017 05/09/2020$0$200
423403/08/2021 09/04/2022$0$300
534507/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  

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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