Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
curiouspbix0
Helper IV
Helper IV

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 ) 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-01 153628.png

For the related .pbix file,pls see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-01 153628.png

For the related .pbix file,pls see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Fowmy
Super User
Super User

@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"
)

 

Fowmy_0-1598777504056.png

________________________

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.