Last non blank date, unfiltered

DAX DAX Calculations
1

Hi,

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)

2

Couple of things here that you might what to adjust.

First when you use dates like this to filter…

image

…you run the risk that you are filtering for very specific dates. That is why having a full date table is key all the time.

That way you can use the month filters in a far more effective way.

It looks like filtering by a month is fine here, so that’s all good, but something to be aware off when filtering by just the day.

image

To get the correct date you can use a formula like this

Last Account Sales Date = 
CALCULATE( MAX( Holdings[Holding date] ), ALL( Accounts[Accounts] ))

Then for the amount

Last Date Market Value = 
VAR Last_Date = [Last Account Sales Date]

RETURN
CALCULATE( [Market value],
    FILTER( ALL( Holdings[Holding date] ),
        Holdings[Holding date] = Last_Date ) )

See results here

image
image.png823×496 14.9 KB

I’ve attached latest here also
TestLastNonBlankPBI.pbix (108.3 KB)