Forum Discussion

chotu27's avatar
chotu27
Post Patron
8 years ago

TomMartens Got it Worked but still it is not showing correct previous values please check attached  Pbix

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi chotu27,

 

Maybe there is a solution you can give it a try. Please check it out in the attachment.

1. In the Query Editor, sort the "SubjectNumber" first, then sort the column "VisitName". 

2. Add an index to keep the order we make in step 1.

3. Add a custom column.

if [Index.1] = 0 then
    [Index.1] else if 
    [VisitName] = #"Added Index"{[Index.1] - 1}[VisitName] then 
    null else 
    [Index.1]

4. Right-click the column "Custom", choose Fill -> Down.

5. Apply the changes, and create a measure.

 

Measure =
VAR lastIndex =
    CALCULATE (
        MAX ( 'Table'[Custom] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SubjectNumber] ),
            'Table'[Custom] < MAX ( 'Table'[Custom] )
        )
    )
RETURN
    IF (
        ISBLANK ( lastIndex ) = FALSE ()
            && HASONEVALUE ( 'Table'[VisitName] ),
        CALCULATE (
            SUM ( 'Table'[SiteRaterScore] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Custom] = lastIndex )
        ),
        0
    )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi chotu27,

     

    Is my solution helpful?

     

    Best Regards,

    Dale