Forum Discussion

sokatenaj's avatar
sokatenaj
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Need Help Creating a Calculated Column Based on Birth Year

Good afternoon,

 

I am struggling here. I tried creating a conditional column or calculated column based on birth year but there is no "BETWEEN" function. I want to say, if Birth Year is between 1927 to 1945 then Mature/Silents ELSE IF Birth Year is between 1946-1964 then Baby Boomer ELSE IF birth year is between..etc You get the picture.

 

 I want it all in 1 column because I have birth year as a column in my table but want to create a new column using that logic above and eventually use it as a slicer. I am stuck! Any help would be appreciated. Please and thank you!

 

  • Mature/Silents.- Born 1927 to 1945
  • Baby Boomer – 1946 to 1964
  • Gen X – 1965 to 1980
  • Millennials – 1981 to 2000 Milennial
  • Gen Z/Boomlet – 2001 forward
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

     

    Generation = SWITCH(
    TRUE(),
    TableName[Birth Year] >= 1927 && TableName[Birth Year] <= 1945, "Mature/Silents",
    TableName[Birth Year] >= 1946 && TableName[Birth Year] <= 1964, "Baby Boomer",
    TableName[Birth Year] >= 1965 && TableName[Birth Year] <= 1980, "Gen X",
    TableName[Birth Year] >= 1981 && TableName[Birth Year] <= 1200, "Millenial",
    TableName[Birth Year] >= 2001, "Gen Z/Boomlet",
    BLANK()
)

     

    Anything before 1927 will remain blank with this formula.

     

    Edit: aww, Vvelarde beat me to it while I was testing something with the new table constructor.

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    sokatenaj

     

    Hi, Try with this DAX

     

    Generation =
SWITCH (
    TRUE (),
    YEAR ( Table1[Birth Date] ) >= 1927
        && YEAR ( Table1[Birth Date] ) <= 1945, "Mature / Silents",
    YEAR ( Table1[Birth Date] ) >= 1946
        && YEAR ( Table1[Birth Date] ) <= 1964, "Baby Boomer",
    YEAR ( Table1[Birth Date] ) >= 1965
        && YEAR ( Table1[Birth Date] ) <= 1980, "Gen X",
    YEAR ( Table1[Birth Date] ) >= 1981
        && YEAR ( Table1[Birth Date] ) <= 2000, "Millenials",
    "Gen Z / Boomlet"
)

    Let me know if was helpful

     

    Victor

    Lima - Peru

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

     

    Generation = SWITCH(
    TRUE(),
    TableName[Birth Year] >= 1927 && TableName[Birth Year] <= 1945, "Mature/Silents",
    TableName[Birth Year] >= 1946 && TableName[Birth Year] <= 1964, "Baby Boomer",
    TableName[Birth Year] >= 1965 && TableName[Birth Year] <= 1980, "Gen X",
    TableName[Birth Year] >= 1981 && TableName[Birth Year] <= 1200, "Millenial",
    TableName[Birth Year] >= 2001, "Gen Z/Boomlet",
    BLANK()
)

     

    Anything before 1927 will remain blank with this formula.

     

    Edit: aww, Vvelarde beat me to it while I was testing something with the new table constructor.

    • sokatenaj's avatar
      sokatenaj
      Icon for Advocate II rankAdvocate II
      9 years ago

      Thank you both!!! I can't believe it was that simple!!! Jeez. I'll keep this one in my back pocket!