# Simple LASTNONBLANK DAX question

### Question

• I am a DAX beginner, and I am trying to get to grips with what I think is a very simple, common semi-additive scenario. I think I must be missing something quite fundamental so please bear with me!

I have an ACCOUNT table which has a 1 to many relationship with an ACCOUNT_BALANCE table. There is a separate date dimension, which has a 1 to many relationship with the ACCOUNT_BALANCE table.

All I am trying to do is calculate the latest balance date for each account. I have the following DAX query:

```DEFINE
MEASURE
ACCOUNT[NUMBER OF BALANCES] = COUNTROWS(RELATEDTABLE(ACCOUNT_BALANCE))
MEASURE
ACCOUNT[LATEST BALANCE DATE] = LASTNONBLANK(DimDate[FullDate], COUNTROWS(RELATEDTABLE(ACCOUNT_BALANCE)))
EVALUATE
(
ACCOUNT,
"Number of Balances", ACCOUNT[NUMBER OF BALANCES],
"Latest Balance Date", ACCOUNT[LATEST BALANCE DATE]
)

```

While the number of balances evaluates correctly, I just get blanks for the latest balance date.

I think I must be missing something quite fundamental about how DAX works in this type of scenario. Any pointers would be much appreciated.

Thanks,

Robert

Sunday, August 18, 2013 12:15 PM

• Having looked at this more closely, my original DAX was correct - my problem was some issues in my sample data.

Moderators - can you delete this question - sorry to anyone who was confused by this.

Thanks,

Robert

Monday, August 26, 2013 8:46 AM

### All replies

• Hi Robert,

Thank you for your question. I am currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

Best Regards,

Elvis Long
TechNet Community Support

Wednesday, August 21, 2013 3:15 AM
• Hi Robert,

Please try to use the following DAX command to see if this works:
DEFINE
MEASURE
ACCOUNT[NUMBER OF BALANCES] = COUNTROWS(RELATEDTABLE(ACCOUNT_BALANCE))
MEASURE
ACCOUNT[LATEST BALANCE DATE] = LASTNONBLANK(DimDate[FullDate], COUNTROWS(RELATEDTABLE('ACCOUNT_BALANCE')))
EVALUATE
(
ACCOUNT,
"Number of Balances", ACCOUNT[NUMBER OF BALANCES],
"Latest Balance Date", ACCOUNT[LATEST BALANCE DATE]
)

I added single quotation marks for COUNTROWS(RELATEDTABLE('ACCOUNT_BALANCE'))

Best Regards,

Elvis Long
TechNet Community Support

Thursday, August 22, 2013 5:04 AM
• Elvis,

Thanks for your response but that made no difference to the result.

Note that the query executes without any error being returned, however the latest balance date column returned is blank.

Does anyone have any similar examples that do work for this kind of semi-additive case? I have searched but have not really found any that combine semi-additive with a separate date dimension.

Thanks,

Robert

Thursday, August 22, 2013 6:55 PM
• Having looked at this more closely, my original DAX was correct - my problem was some issues in my sample data.

Moderators - can you delete this question - sorry to anyone who was confused by this.

Thanks,

Robert

Monday, August 26, 2013 8:46 AM
• Hi Robert,

Thanks for confirm this, I will close this thread. Please let me know if you have any questions.

Have a nice day!

Best Regards,

Elvis Long
TechNet Community Support

Monday, August 26, 2013 8:57 AM