Forum Discussion

Sachy123's avatar
Sachy123
Helper V
6 years ago
Solved

Get latest Balance

I have a COA table which comes as a result of direct query in the following format. DateID COA Balance 1 1001 50 1 1002 60 2 1003 100 2 1001 30 3 1002 80 4 1003 60...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try like

    LASTNONBLANKVALUE(Table[DateID], max(Table[Balance]))
    Or
    LASTNONBLANKVALUE(Table[DateID], sum(Table[Balance]))

     

    Date Join should be on date ID. You can try using Date[Date] in place of Table[Date ID]

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Will you be selecting Data ID4 in the filter/slicer or do you want the DAX formula to pick up the last balance of each COA automatically?  Also, do you have actual date entries in the DateID column?  I'd request you to share the actual date format that you have in the DateID column.

  • Sachy123's avatar
    Sachy123
    Helper V
    6 years ago

    Hi Ashish_Mathur 

    Yes will be selecting the dateid from the filter. well the date-id is coming via date dimension which has got format dd-mm-yyyy

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • Sachy123's avatar
        Sachy123
        Helper V
        6 years ago

        Hi Ashish_Mathur 

        Thanks!   in your file the logic is working as expected.

        but when I translate it in to my data, it throws me an error : An invalid numeric representation of a date value was encountered.

         

        The date format in the date table was as follows 2016-06-13 00:00:00.000

         

        Also , the COA data is a direct query..