none
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
    ADDCOLUMNS
    (
    	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

Answers

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.

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, August 21, 2013 3:15 AM
    Moderator
  • 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
    ADDCOLUMNS
    (
     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
    Moderator
  • 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
    Moderator