I am having trouble figuring out how to write a DAX formula that returns values for the last non blank date. Any help is greatly appreciated!
What I am trying to acheive is to remove the need for the end user querying the model to have to make a date selection in order to get values corresponding to the latest date that any account had a market value. So if no date selection is made I just want the values to default to the latest date where any account had a value.
The measure I have now works fine when looking at the totals but falls short when I drill down/ filter on a specific account. In that case it shows the last non blank value for that specific account, even if there is an other account that has a value on a later date.
I used a Lastnonblank function and it works in the totals, but not when I drill down to the specific accounts in this example. I have tried some different variants but none rend the output I am looking for.
Attached is a test model that (I hope) shows what I am looking to acheive and where I am falling short at the moment.
Market value (wrong lastnonblank) is the measure I have today, that does not work when looking on a specific account
Market value (corrected lastnonblank) is the measure where I have tried modeling the above measure in a different way, but to no avail.
Thanks in advance!
TestLastNonBlankPBI.pbix (109.5 KB)