Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
)
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!
Solved! Go to 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.
Tried to format it in DAX Formatter and get the error at Return
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]
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |