topic Re: Calculated columns for last and second last values for a measurement in DAX Commands and Tips

Calculated columns for last and second last values for a measurement

Anonymous — Tue, 08 Nov 2022 14:46:51 GMT

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!

Re: Calculated columns for last and second last values for a measurement

daXtreme — Tue, 08 Nov 2022 16:53:36 GMT

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]

Re: Calculated columns for last and second last values for a measurement

Anonymous — Tue, 08 Nov 2022 17:43:14 GMT

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

Re: Calculated columns for last and second last values for a measurement

Anonymous — Tue, 08 Nov 2022 17:46:24 GMT

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

Re: Calculated columns for last and second last values for a measurement

Anonymous — Wed, 09 Nov 2022 14:40:22 GMT

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.