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 there,
I am trying to create a "Generation" Column based on date ranges. I am having trouble writing a formula that says "if date >= 1/1/27 and date <= 12/31/37 then "Generation 1" else if "if date >= 1/1/38 and date <= 12/1/48 then "Generation 2 " ..... These are not the actual date ranges but you get what I mean. The end goal is to create a filter for my report so people can choose from a list of generations.
I would love some help or guidance with this and some suggestions as to the best way I can do this.
Thank you so much.
Solved! Go to Solution.
@alexlibby try something like this:
Gen Column =
VAR __date = YourTable[YourDate]
RETURN
SWITCH ( TRUE(),
__date >= DATE ( 2027,1, 1 ) && __date <= DATE ( 2037,12, 31 ), "Gen 1",
__date >= DATE ( 2038,1, 1 ) && __date <= DATE ( 2048,12, 31 ), "Gen 2",
"Gen other"
)
✨ Follow us on LinkedIn
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.
@alexlibby it should be something like this:
Gen Column =
VAR __year = YourTable[YourYearColumn] )
RETURN
SWITCH ( TRUE(),
__year >= 2001, "Gold",
__year >= 1977, "Silver",
__year >= 1962, "Bronze",
"Others"
)
✨ Follow us on LinkedIn
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.
@alexlibby it should be something like this:
Gen Column =
VAR __year = YourTable[YourYearColumn] )
RETURN
SWITCH ( TRUE(),
__year >= 2001, "Gold",
__year >= 1977, "Silver",
__year >= 1962, "Bronze",
"Others"
)
✨ Follow us on LinkedIn
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.
Once again, thank you!!!!
@alexlibby not sure what you mean by yyyy format, do you have only year value in the column?
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.
Hi there,
Yes, I have a column that has class years (2001, 1976, 1981, etc) and I want to create a similar formula that says if between 2001 - 2020 then "gold", if between 1976 - 1962, "Bronze", etc.
They are currently formatted as whole numbers even tho they are years.
What additional information can I provide?
@alexlibby try something like this:
Gen Column =
VAR __date = YourTable[YourDate]
RETURN
SWITCH ( TRUE(),
__date >= DATE ( 2027,1, 1 ) && __date <= DATE ( 2037,12, 31 ), "Gen 1",
__date >= DATE ( 2038,1, 1 ) && __date <= DATE ( 2048,12, 31 ), "Gen 2",
"Gen other"
)
✨ Follow us on LinkedIn
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.
Hi There- this absolutely worked! May I ask how I would create the same formula using a date that is YYYY? In my dataset it's formatted as a Whole Number. It got wonky when I tried to convert it to a YYYY date format. Is there a way to create a "Alum definition" column using YYYY ranges? Thank you so much!
This worked beautifully! Thank you so much!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |