Relative age?

[grggmrtn]

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 perfectly. I've also created a column that puts everyone in an age "group" via a simple switch.

BUT - I now need to create a diagram, where I list the number of customers per year, split into age groups - it would work best in an area chart, with the year as axis, age group as legend, and the number of customers as a value.

People age... meaning my age column is a bit useless. Unless I can figure out how to make that age (and the age group) relative?

I have a relative year column in my date (RelativeYear = Date[Year] - Year(Today())), but that's not really helpful in the chart...

Any ideas how I can tackle this?

[vanessafvg]

@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]

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]

Can you share how the age grouping is done?

[grggmrtn]

@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]

@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]

This is what I'm going for - just with "age group" instead of what's currently there.

[vanessafvg]

@grggmrtn  apologies but im struggling to understand what you looking for, surely it should be easy to get the age of the current year with a count?   i dont quiet understand your challenge apologies if i am understanding you incorrectly

can you demonstrate what you expecting your visual to look like

[grggmrtn]

@vanessafvgI understand it's a bit complicated, sorry 🙂

I'm looking for a visualsation just like the one you show there, except that I need it to be a clustered column chart, with the year as axis.

So in the chart I've attached here, the x-axis is the year and age group, and the y-axis is the number of people. Same number of total people for each year, but of course they're all two years younger in 2018 than now, so the age group distribution will be different.

Is that making any more sense?

[vanessafvg]

@grggmrtn ok I get it, but the question is where is the relative date sitting,  is that from the slicer?  how does the relative date relate to the person?  You obviously need to calculate it based on the relative year.  But what is that relative year based on in your date?  the date in dim date that is linked to your factless fact?   

[grggmrtn]

@vanessafvgI think I'm going to have to end this thread with a "thanks for trying" - I'm beginning to realise that the complexity of the datamodel is going to make it pretty much impossible to explain the problem without sharing a LOT more than just dummy data, and that would break too many rules 😉

Sorry, but I really appreciate your effort anyhow 🙂

[Stachu]

if I understand the problem correctly you have a star schema model, and you want to calculate the age of a customer based on a Calendar table rather than fixed value (TODAY()), is that correct?
try this code

Relative Age = 
VAR __CurrentDate = MAX(Dim_Date[KEY_Date])
DATEDIFF(
    MAX(Dim_Customer[Birthday]); 
    __CurrentDate;
    YEAR
    )

 it should work as long as Year in the X axis is from the Dim_Date table

[grggmrtn]

@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.