Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
PowerBI87
Frequent Visitor

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
1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
az38
Community Champion
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
collinq
Super User
Super User

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!



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.