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   S1         1.3                   08/27/2020 9:00 am 

Brooklyn   S1         1.8                   08/27/2020 8:30 am 

Brooklyn   S1         1.5                   08/27/2020 8:00 am 

Brooklyn   S1         1.9                   08/27/2020 7:30 am 

Brooklyn   S1         1.3                   08/27/2020 7:00 am 

Brooklyn  S2         3.4                   08/27/2020 8:30 am 

Brooklyn  S2         4.5                   08/27/2020 8:00 am 

Brooklyn  S2         5.5                   08/27/2020 7:30 am 

 

Output

 

Brooklyn S1 Up Arrow  ( comparing 8.30 am entry (1.8) to 8 (1.5) am entry ) 

Brookyn S2 Down Arrow ( comparing 8.00 am entry (4.5) to 7.30 (5.5) am entry ) 

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!

2 Replies

  • 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

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!