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]

     

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..

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

That does not look like a Date format.  In the Query Editor, ensure that you set the format of that column to Date.

  • Sachy123's avatar
    Sachy123
    Helper V
    6 years ago

    Ashish_Mathur  The transformation is not allowed in direct query to change column type. So the COA table is a result of direct query and the date dimension , I have pulled in the entire data set.