Column to Calculate Year

[PowerBI87]

Hi,

Can i please get help to write a function that will output the column in the right?

Company	Product	Year	Output
ABC Corp	A	2017	First Year
GHY Corp	A	2018	First Year
YYZ Corp	B	2020	Second Year
ABC Corp	A	2018	Second Year
ABC Corp	A	2019	Third Year
YYZ Corp	B	2019	Firs Year
HYU Corp	A	2020	First Year

[az38 · Accepted solution]

Hi @PowerBI87 

try to create column

Output = 
var r = RANKX(FILTER(ALL('Table'), 'Table'[Company] = EARLIER('Table'[Company]) && 'Table'[Product] = EARLIER('Table'[Product])),
'Table'[Year], , ASC)
var textRank = SWITCH(r,
1, "First",
2, "Second",
3, "Third",
BLANK())
RETURN
textRank & " Year"Q

[collinq]

Hi @PowerBI87 ,

To help out I would have to understand how the output is being determined.  It is obviously not the Year = Output so what are the variables you are trying to use?

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[parry2k]

@PowerBI87 add a column with following expression

Output = 
VAR __minYear = CALCULATE ( MIN ( Table[Year] ), ALLEXCEPT ( Table, Table[Company] ) ) 
RETURN Table[Year] - __minYear ) + 1

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡