Forum Discussion

PowerBI87's avatar
PowerBI87
Frequent Visitor
6 years ago
Solved

Column to Calculate Year

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 ...
Need Help
  • az38's avatar
    az38
    6 years ago

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

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.⚡