Forum Discussion

grggmrtn's avatar
grggmrtn
Post Patron
6 years ago

Relative age?

Hey everyone. So, I've calculated the age of my "customers" by a simple dax:   Age = DATEDIFF( MAX(Dim_Customer[Birthday]); TODAY(); YEAR )   and it, of course, works perfect...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
6 years ago

grggmrtn  what is the structure of your data and how would you be calling this,  if you can give me a sample of your data that would be useful, or dummy data

  • grggmrtn's avatar
    grggmrtn
    Post Patron
    6 years ago

    Hi vanessafvg 

    The structure is just about as complicated as they come, but I'll try and explain the minimum...

     

    We have a "factless" fact table, that's related to all dimensions by a common key (many to one, bidirectional). For example:
    Fact_table has columns KEY_Customer and KEY_Date.
    Dim_Customer has columns KEY_Customer, CustomerID, Birthday, Gender.

    Dim_Date has columns KEY_Date

     

    I also have my main [date] table written in DAX (Calenderauto()) which is related to Dim_Date (one to one, bidirectional) which I use to slice the data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Can you share how the age grouping is done?

      • grggmrtn's avatar
        grggmrtn
        Post Patron
        6 years ago

        Anonymoussure:

        Age group = 
SWITCH(
    TRUE();
    [Age] < 20; "19 (and under)";
    [Age] < 25; "20-24";
    [Age] < 30; "25-29";
    [Age] < 35; "30-34";
    [Age] < 40; "35-39";
    [Age] < 45; "40-44";
    [Age] < 50; "45-49";
    [Age] < 55; "50-54";
    [Age] < 60; "55-59";
    [Age] < 65; "60-64";
    [Age] < 70; "65-69";
    [Age] < 75; "70-74";
    [Age] < 80; "75-79";
    [Age] < 85; "80-84";
    [Age] < 90; "85-89";
    "90 (or over)"
)