Forum Discussion

drivas771994's avatar
drivas771994
Helper II
7 years ago

How to create a measure to pull previous data

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...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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%20data.pbix?dl=0

 

Best Regards,

Lin

 

drivas771994's avatar
drivas771994
Helper II
7 years ago

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's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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