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
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)"
)
    • vanessafvg's avatar
      vanessafvg
      Community Champion
      6 years ago

      grggmrtn  ok so just so that i understand what you asking

       

      you want to take the year (sales year?) and work out how old they were in that year?

      • grggmrtn's avatar
        grggmrtn
        Post Patron
        6 years ago

        vanessafvgYes, exactly. I want to take the current year (not sales year) and find out how many customers there were in each age group - their age at that time.