Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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"
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
Thank you Xiaoxin, Worked like a charm 👍
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |