Reference previous row to get value

[vrocca]

Hi all,

I have a dataset with Vehicles, Dates, and Odometer. I'm trying to create a calculation that returns the prior odometer reading for each row. I have followed several examples on the web and they work for the most part, but my issue is that I have some bad data where the odometer readings are incorrect and therefore not incremental (i.e. for 4/1/17 it may read 23,500  and then for 4/5/17 it may read 22,500). 

The formula I've been working on looks like this:

Previous Odometer := CALCULATE(MAX(Transactions[Odometer #]),ALL(Transactions),Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date])  )

What happens in the scenario where the odometer has a bad reading is that it keeps using that value for all future readings, and throws off the calculations from that point forward.

Example:

Transaction Date          Odometer#          Previous Odometer

02/08/17                      66770                   66320

02/15/17                      97315 (bad data)  66770                              

02/20/17                      67630                   97315

02/27/17                      68056                   97315   <-- here is my issue. this should read 67630. Value keeps repeating for all rows below after this

03/04/17                      68483                   97315

Any suggestions on how I need to modify my DAX formula so that I don't get the same value being repeated?

[NeilAm12]

---

@vrocca wrote:

Hi all,

I have a dataset with Vehicles, Dates, and Odometer. I'm trying to create a calculation that returns the prior odometer reading for each row. I have followed several examples on the web and they work for the most part, but my issue is that I have some bad data where the odometer readings are incorrect and therefore not incremental (i.e. for 4/1/17 it may read 23,500  and then for 4/5/17 it may read 22,500). 

The formula I've been working on looks like this:

Previous Odometer := CALCULATE(MAX(Transactions[Odometer #]),ALL(Transactions),Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date])  )

What happens in the scenario where the odometer has a bad reading is that it keeps using that value for all future readings, and throws off the calculations from that point forward.

Example:

Transaction Date          Odometer#          Previous Odometer

02/08/17                      66770                   66320

02/15/17                      97315 (bad data)  66770                              

02/20/17                      67630                   97315

02/27/17                      68056                   97315   <-- here is my issue. this should read 67630. Value keeps repeating for all rows below after this

03/04/17                      68483                   97315

Any suggestions on how I need to modify my DAX formula so that I don't get the same value being repeated?

---

Hello All,

I think my issue is the same with this but with a column with different data.

I want my RRA column to get the previous data of Data-Xi column based on Concatenate column data and blank the 1st data.

I hanve date field and Combine column of Mini Company and Machine.

i try different formula but error will show.

Below is my code:

RRA = (CALCULATE(MAX(Test[Data-Xi]),(DB[Concatenate]),FILTER(DB,DB[Entry Date/Time]<DB[Entry Date/Time])))

[vrocca · Accepted solution]

I was able to get something to work by creating a Row ID using RANKX, and then another column (Prior Row ID) using EARLIER to get the previous Row ID. I then added this to my original Previous Odometer calculation as an additional filter:

Row ID := RANKX( ALL(Transactions[Transaction Date]), T ransactions[Transaction Date], , ASC, Dense)

Previous Row ID := CALCULATE

                                      (  

                                       MAX(Transactions[Row ID]),

                                       ALL(Transactions),Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),

                                       Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date])

                                      )

Previous Odoometer =
CALCULATE(

                    MAX(Transactions[Odometer #]),

                    ALL(Transactions),

                    Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),

                    Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date]),

                    Transactions[Row ID] = EARLIER(Transactions[Previous Row ID]) )
)

Curious to see if there is a much simpler way to achieve the same results....

[Anonymous]

https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298

help me out on this

[sirros_iot]

Hello vrocca

I need to find the previous record to get the difference between the time. 

My problem is that I can't use calculated columns, I only can use measures to solve this problem.

Can I find out the solution only with measures?

thanks,

Diego Schneiders Lutckmeier

[vrocca]

Hey Diego  - what is the reason as to why you are not able to create a calculated column? Are you connecting to a live cube? Could you create the calculated columns in the source?

[sirros_iot]

I'm trying to do this:

.test =
VAR X =
CALCULATE(SUM(RealTimeData[_ts]);
FILTER(ALL(RealTimeData);

SUMX(
FILTER(RealTimeData;RealTimeData[_ts] >= EARLIER(RealTimeData[_ts]));
RealTimeData[_ts])))

// VAR UnixDays = X / (60*60*24)
// VAR Times = ((DATEVALUE("1/1/1970") + UnixDays))

RETURN
// DATEDIFF(MIN(RealTimeData[tempo]);Times;SECOND)
X

This is my data: I have timestamp transformed too.

[vrocca]

What are you trying to return? What is it that you are trying to calculate?

[sirros_iot]

Sorry kkk, I'm trying to get the difference between the times in the table. It will be nescesary to know if one machine is working in the time or not. Let's see, I have the average time from the each ID, and I need to compare to the time difference between the rows.

Like this,
IF ( [time_difference] > 2 * [average_time]; "stopped"; "working")

Thanks for the reply

[vrocca]

Hey Diego - I spent some time looking for alternatives, but I'm not sure of a good way to arrive at what you are trying to do without creating a new column. The EARLIER function only works on columns, so the logic above wouldn't work for you. I'll see if I can test some other things and will let you know if I find something. I

Let me know if you find a workaround.

[sirros_iot]

@vrocca, I am trying to do it:

.Tdiff = CALCULATE( VALUES(RealTimeData);
	FILTER(ALL(RealTimeData);

	VAR Previoustime =
	 CALCULATE(VALUES(RealTimeData[_ts]);
		 FILTER(ALL(RealTimeData);
		 RealTimeData[id] = EARLIER(RealTimeData[id])-1
		 ))
	
RETURN
         (RealTimeData[_ts] - Previoustime)))

[_ts] = Equal to timestamp value: like this (9428293029)

I think that with this measure I could have the time in seconds.

[sirros_iot]

Thank you for the answer, I am searching for a way but it isn't very easy, right.

Is it right that I can't use calculated columns? Is that because of the Power BI online?

I'll keep you informed, thanks for your effort. 

[sirros_iot]

Yes, I'm connected to the live cube(Power BI online?), my data is coming from a device( arduino) and I'm not sure about the calculated columns. I tried to create but I didn't get anything from the power BI online. Thank you for the reply.