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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Share the link from where i can download the PBI file or paste the Table here so that i can take it to an Excel file.  More importantly, please show the expected result in another column.