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)"
)
      • 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?