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?

 

CompanyProductYearOutput
ABC CorpA2017First Year
GHY CorpA2018First Year
YYZ CorpB2020Second Year
ABC CorpA2018Second Year
ABC CorpA2019Third Year
YYZ CorpB2019Firs Year
HYU CorpA2020First 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

3 Replies

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

  • collinq's avatar
    collinq
    Super User
    6 years ago

    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!
  • az38's avatar
    az38
    Community Champion
    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