Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
Solved! Go to Solution.
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.
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |