Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Bumping this up....
I think you can use
Last Balance =
LASTNONBLANKVALUE ( 'Table'[Report Month], SUM ( 'Table'[Duplicate balance] ) )
@johnt75
I have tried using this one:
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
@v-yiruan-msft
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |