Forum Discussion

vini_udenia's avatar
vini_udenia
Icon for Helper I rankHelper I
3 years ago
Solved

Create a new column based on Previous column rank

Hi, 

I'm looking for a way to create a new column in my PBI report based on the  value in previous session. So for School 505 # of candidates in current session is 100, the new column should display the # of candidates in the previous session ie (June 2022 - identified by using Session Rank -1) to show 55.

 

I know can be done using DAX , any help appreciated! TIA

 

SchoolYear MonthSesion Rank# Candidates# Candidates Previous Session
5052022JUNE1550
5052022DEC210055
5062022JUNE1650
5062022DEC27065
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi vini_udenia,

    You can use School and Year field values as group and use current session rank as condition to lookup the previous records.

    formula =
MAXX (
    FILTER (
        Table,
        [School] = EARLIER ( Table[School] )
            && [Year] = EARLIER ( Table[Year] )
            && [Sesion Rank]
                = EARLIER ( Table[Sesion Rank] ) - 1
    ),
    [# Candidates]
) + 0

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi vini_udenia,

    You can use School and Year field values as group and use current session rank as condition to lookup the previous records.

    formula =
MAXX (
    FILTER (
        Table,
        [School] = EARLIER ( Table[School] )
            && [Year] = EARLIER ( Table[Year] )
            && [Sesion Rank]
                = EARLIER ( Table[Sesion Rank] ) - 1
    ),
    [# Candidates]
) + 0

    Regards,

    Xiaoxin Sheng

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    Power BI has no concept of "previous".  You either need to provide indexing guidance, or use the new OFFSET function to indicate what you mean by "previous"