Calculated columns for last and second last values for a measurement

[Anonymous]

Hello,

I am working on a project where is needed the last and second last readings for an asset and get the difference between values, so we can make some decisions afteerwards based on the difference. I created two calculated columns, but they are very expensive, especially the secondlastvalue formula. I shared a screenshot with the readings (value), dates (taken_date), where(taken_at_location), reading type (UT Reading Minimum) and asset. Also, the formulas for my last and second last values.

latestvalue =
VAR last_date =
CALCULATE ( MAX ( Readings[taken_date] ), Readings[value] <> BLANK () )
RETURN
CALCULATE (
SELECTEDVALUE ( Readings[value] ),
Readings[taken_date] = last_date
)

secondlastvalue =

VAR second_last_date =

    CALCULATE (

        MAX ( Readings[taken_date] ),

        Readings[value] <> BLANK ()

            && Readings[taken_date] < MAX ( Readings[taken_date] )

    )

RETURN

    CALCULATE (

        SELECTEDVALUE ( Readings[value] ),

        Readings[taken_date] = second_last_date

    )

You can see that for each location there are two readings (now they are the same, but is not the standard) for two dates. Can somebody take a look at the formulas, maybe there is another way to get those values? 

Much appreciated!

[Anonymous]

Tried to format it in DAX Formatter and get the error at Return

[daXtreme]

Here are some measures you can try... Measures, not calculated colums.

// Hidden helper measure; easier to debug
[_LatestDate] =
var Output =
    CALCULATE(
        MAX( Readings[taken_date] ),
        keepfilters(
            not isblank( Readings[value] )
    )
return
    Output

// Hidden helper measure
[_SecondLatestDate] =
var LatestDate = [_LatestDate]
var Output =
    CALCULATE (
        MAX ( Readings[taken_date] ),
        keepfilters(
            Readings[taken_date] < LatestDate
        )
    )
return
    Output

// This works on the assumption that
// there will only be at most 1 value
// for the LatestDate. If there are
// many, BLANK will be returned since
// this is the behaviour of SELECTEDVALUE.
// Same applies to the second measure.
[LatestValue] =
VAR LastestDate = [_LatestDate]
var Output = 
    CALCULATE(
        SELECTEDVALUE( Readings[value] ),
        keepfilters( 
            Readings[taken_date] = LatestDate
        )
    )
return
    Output
    
[SecondLatestValue] =
VAR SecondLastestDate = [_SecondLatestDate]
var Output =
    CALCULATE (
        SELECTEDVALUE ( Readings[value] ),
        keepfilters(
            Readings[taken_date] = SecondLatestDate
        )
    )
return
    Output
    
[Difference] = [LatestValue] - [SecondLatestValue]

[Anonymous · Accepted solution]

Hi daXtreme, I manipulated the formulas to work, it is a bit faster. I like using the lastdate and secondlastdate measures. Thank you for your input.

[Anonymous]

Hi daXtreme, for some reason I get an error regarding the Return syntax!? 

_LatestDate] =
var Output =
    CALCULATE(
        MAX( Readings[taken_date] ),
        keepfilters(
            not isblank( Readings[value] )
    )
return
    Output