DAX column required to return previous value based on second to last date

[julesdude]

Hi,

I have a table a little like this:

Asset Reference	Valuation Date	Valuation Amount
aaa	12/08/2022	100
aaa	12/04/2021	200
bbb	22/02/2021	300
bbb	15/09/2022	400

I am trying to apply a DAX column that will take the asset reference for the row and the valuation date, and display the valuation amount for the previous valuation date for that asset reference.

I have this:

Previous Valuation = 
VAR CurrentAssetRef = Asset_Valuation[Asset Reference]
VAR CurrentValuationDate = Asset_Valuation[Valuation Date]
VAR secondtolastdate = CALCULATE(MAX(Asset_Valuation[Valuation Date]), Asset_Valuation[Asset Reference] = CurrentAssetRef, Asset_Valuation[Valuation Date] < CurrentValuationDate)
RETURN
CALCULATE (
    MAX (Asset_Valuation[Asset Value] ),
    Asset_Valuation[Asset Reference] = CurrentAssetRef,
    Asset_Valuation[Valuation Date] = secondtolastdate
)

However, this is returning a column with blank values, and I am not sure why.

I have adapted to include the ALL() command after the MAX statement, but this returns blanks also.

Not sure what I am doing wrong but any help appreciated.

[johnt75 · Accepted solution]

You can use

Previous Valuation = 
VAR CurrentAssetRef = Asset_Valuation[Asset Reference]
VAR CurrentValuationDate = Asset_Valuation[Valuation Date]
VAR secondtolastdate =
    CALCULATE (
        MAX ( Asset_Valuation[Valuation Date] ),
        ALLEXCEPT ( Asset_Valuation, Asset_Valuation[Asset Reference] ),
        Asset_Valuation[Valuation Date] < CurrentValuationDate
    )
RETURN
    CALCULATE (
        MAX ( Asset_Valuation[Valuation Amount] ),
        ALLEXCEPT ( Asset_Valuation, Asset_Valuation[Asset Reference] ),
        Asset_Valuation[Valuation Date] = secondtolastdate
    )

[julesdude]

Hi @johnt75 

Thanks for this. Unfortunately still returns blanks.

[johnt75]

It works for me, as in the attached. What other columns do you have in the table ?

[julesdude]

Hi @johnt75 

My bad - i needed to scroll down a good few rows to start getting the rows where there actually was a previous value available! Your solutions works.

Would you mind a quick comment on what your solution is doing that mine wasn't previously?

[johnt75]

The key is the ALLEXCEPT, which removes any filters on the table except for the reference. When you do a CALCULATE from a row context, like you have in a calculated column, every column from the table is used as a filter which means that the valuation itself would be part of the filter, as well as any other columns the table might have.

It also best practice to use ALLEXCEPT in calculated columns because if you add multiple calculated columns you can get a circular dependency error.

[julesdude]

Makes sense, Thanks for the explanation and the solution.