Create a new column based on Previous column rank

[vini_udenia]

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

School	Year	Month	Sesion Rank	# Candidates	# Candidates Previous Session
505	2022	JUNE	1	55	0
505	2022	DEC	2	100	55
506	2022	JUNE	1	65	0
506	2022	DEC	2	70	65

[lbendlin]

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"

[v-shex-msft · Accepted solution]

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

[vini_udenia]

Thank you Xiaoxin, Worked like a charm 👍