Forum Discussion

rajivraina's avatar
rajivraina
Helper II
7 years ago
Solved

Need Help: Show second to last value

Hi all,   I was hoping someone could help me figure out a way to show the second to last value for a certain field.    For example, I have country credit ratings which are a text field (D through...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    You could probably create a Calculated column like this:

     

    Previous Rating = 
VAR CurrentDate = 'Table'[Date]
VAR CurrentCountry = 'Table'[Country]
VAR AllPreviousRatingsTable =
    FILTER (
        'Table',
        'Table'[Country] = CurrentCountry
            && 'Table'[Date] < CurrentDate
    )
VAR PreviousRatingTable =
    TOPN ( 1, AllPreviousRatingsTable, 'Table'[Date], DESC )
VAR PreviousRating =
    MAXX ( PreviousRatingTable, 'Table'[Rating] )
RETURN
    PreviousRating
AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

You could probably create a Calculated column like this:

 

Previous Rating = 
VAR CurrentDate = 'Table'[Date]
VAR CurrentCountry = 'Table'[Country]
VAR AllPreviousRatingsTable =
    FILTER (
        'Table',
        'Table'[Country] = CurrentCountry
            && 'Table'[Date] < CurrentDate
    )
VAR PreviousRatingTable =
    TOPN ( 1, AllPreviousRatingsTable, 'Table'[Date], DESC )
VAR PreviousRating =
    MAXX ( PreviousRatingTable, 'Table'[Rating] )
RETURN
    PreviousRating
rajivraina's avatar
rajivraina
Helper II
7 years ago
AkhilAshok wrote:

You could probably create a Calculated column like this:

 

Previous Rating = 
VAR CurrentDate = 'Table'[Date]
VAR CurrentCountry = 'Table'[Country]
VAR AllPreviousRatingsTable =
    FILTER (
        'Table',
        'Table'[Country] = CurrentCountry
            && 'Table'[Date] < CurrentDate
    )
VAR PreviousRatingTable =
    TOPN ( 1, AllPreviousRatingsTable, 'Table'[Date], DESC )
VAR PreviousRating =
    MAXX ( PreviousRatingTable, 'Table'[Rating] )
RETURN
    PreviousRating


Thank you this worked perfectly!!