Forum Discussion

curiouspbix0's avatar
curiouspbix0
Helper IV
6 years ago
Solved

Compare Previous to Prior Value

Hi DAXerts,   Spare Dax to compare previous value to prior value for the most recent time entry for a given City and Station.  Input Table  City         Station   Measurement  Time  Brooklyn   S...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  curiouspbix0 ,

     

    Create a column as below:

    Arrow = 
var _previoustime=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[Time]<EARLIER('Table'[Time])&&'Table'[City]=EARLIER('Table'[City])&&'Table'[Station]=EARLIER('Table'[Station])))
 var _previousmeasure=CALCULATE(MAX('Table'[Measurement]),FILTER('Table','Table'[Time]=_previoustime&&'Table'[City]=EARLIER('Table'[City])&&'Table'[Station]=EARLIER('Table'[Station])))
Return
IF(_previousmeasure=BLANK(),BLANK(),IF('Table'[Measurement]>_previousmeasure,UNICHAR ( 9650 ), UNICHAR ( 128315 ) ))

    And you will see:

    For the related .pbix file,pls see attached.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Fowmy's avatar
Fowmy
Super User
6 years ago

curiouspbix0 

Use the following measure to compare a measurement 

Measure = 
VAR LatestTime = MAX(TABLE01[Time])
VAR PRE1 = MAXX(FILTER(VALUES(TABLE01[Time]),TABLE01[Time] <LatestTime),TABLE01[Time])
VAR PRE2 = MAXX(FILTER(VALUES(TABLE01[Time]),TABLE01[Time] <PRE1),TABLE01[Time])
RETURN 
IF(
    CALCULATE(MAX(TABLE01[Measurement]),TABLE01[Time]=PRE1) > 
    CALCULATE(MAX(TABLE01[Measurement]),TABLE01[Time]=PRE2),
    "Up",
    "Down"
)

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply šŸ™‚

YouTube  LinkedIn