Forum Discussion

chotu27's avatar
chotu27
Icon for Post Patron rankPost Patron
8 years ago
Solved

How do i get previous value with legend on matrix visual

HI,

 

I need calculation for getting previous record with respect to legend kept in matrix visual

 

Sample Data is here

 

see the below image

    

Explanation : I need previous score calculation based on legend kept

   Ex : Such for ID: 1001 Visit2 = 30(Previous Score) based on Judgement, Visit2 = 23(Previous Score) based on Memory

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi chotu27

    Modify formula with the following

    previous score =
CALCULATE (
    MAX ( [Score] ),
    FILTER (
        ALL ( 'previous visits' ),
        [Item Text] = EARLIER ( [Item Text] )
            && [Index]
                = EARLIER ( [Index] ) - 4
            && [Subject number] = EARLIER ( 'previous visits'[Subject number] )
    )
)

     

     

    Best Regards

    Maggie

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi chotu27

    First create an index column from 1,
    Next create a calculated column
    previous score = CALCULATE(MAX([Score]),FILTER(ALL('previous visits'),[Item Text]=EARLIER([Item Text])&&[Index]=EARLIER([Index])-4))
     
    Best Regards
    Maggie
    • chotu27's avatar
      chotu27
      Icon for Post Patron rankPost Patron
      8 years ago

      v-juanli-msft  Hey Thanks it is working well but when i get new ID it is taking previous score from previous id please see the screenshot below

       

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi chotu27

        Modify formula with the following

        previous score =
CALCULATE (
    MAX ( [Score] ),
    FILTER (
        ALL ( 'previous visits' ),
        [Item Text] = EARLIER ( [Item Text] )
            && [Index]
                = EARLIER ( [Index] ) - 4
            && [Subject number] = EARLIER ( 'previous visits'[Subject number] )
    )
)

         

         

        Best Regards

        Maggie