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/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

Need Help

4 Replies

  • 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

         

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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