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...
johnt75's avatar
johnt75
Super User
3 years ago

I think you can use

Last Balance =
LASTNONBLANKVALUE ( 'Table'[Report Month], SUM ( 'Table'[Duplicate balance] ) )
  • KW123's avatar
    KW123
    Helper V
    3 years ago

    johnt75 Thanks! Unfortunately that returns blanks 

  • KW123's avatar
    KW123
    Helper V
    3 years ago

    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) 
    • 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