How to create a measure to pull previous data

[drivas771994]

Hi,

I'm trying to create a measure to pull some previous data in a separate row to be able to calculate. For example, I have an athlete that takes a test 5 different  times on dates , 1/6/2018, 2/24/2018, 3/18/2018, 4/21/2018, and 5/30/2018. Can I create a measure to compare the test score from 5/30/2018 with only 4/21/2018. And if so would I then be able to create a measure to subtract the difference between the two to see the change between the two scores.

Regards,

Daniel Rivas

[v-lili6-msft]

HI,@drivas771994

        Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[v-lili6-msft]

hi,@drivas771994

     After my research, you can do do these follow my steps like below:

Step1:

add two measure like this:

last score = var lastda= CALCULATE(MAX(Table2[Date]), FILTER (          ALL ( Table2[Date]),  Table2[Date] < MAX ( Table2[Date] ))) return
CALCULATE(MAX(Table2[SCORE]),Table2[Date]=lastda)

difference = 
var lastda= CALCULATE(MAX(Table2[Date]), FILTER (          ALL ( Table2[Date]),  Table2[Date] < MAX ( Table2[Date] ))) return
var lastsc=CALCULATE(MAX(Table2[SCORE]),Table2[Date]=lastda) return

CALCULATE(MAX(Table2[SCORE])-lastsc)

Result:

here is demo,please try it.

https://www.dropbox.com/s/rz1231dty5isv4p/How%20to%20create%20a%20measure%20to%20pull%20previous%20d...

Best Regards,

Lin

[drivas771994]

Hi Lin,

Thank you for your response. Unfortunately, the last score formula is giving me the same score as the day of. 

[v-lili6-msft]

hi,@drivas771994

                    After my research, I replicate your formula on my own report, it works well. For your error ,It should be there is no relationship between Dates Table and Table1.

for example.

 then result:

So, please check these 

1. if date column in your visual is from Table1

then use table1 date column in the formula instead of Date Table [Date]

2. and if date column in your visual is from Date Table

then create the relationship between Table1 and Date Table, and cross filter direction is Both

If it is not your case, please share your demo pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin