Calculating Age, Age Grouping, and Age as of

[TabathaN]

I have a three part question. The end result I'm looking for is the ability to take a column containing a DOB (m/dd/yyyy) and display age in years, age groupings, and also have the age be dynamic so that the age will reflect correctly based on any date criteria.

For Example: 

Customer 1 DOB = 8/18/1997
Customer 2 DOB = 4/10/1964
Customer 3 DOB = 7/28/2015

Grouping Categories

1-20

21-40

41-60

61+

I would like a column that displays age in years and another column that displays the appropriate age grouping.

If all three of these customers bought a product today (Nov, 1 2018) and I ran the data today, I would expect results of:

Customer 1: 21 and grouping 21-40

Customer 2: 54 and grouping 41-60

Customer 3: 3 and grouping of 1-20

If the same three people bought product on Sept 15th, 2017, and I was running data today (Nov 1, 2018) for 09/15/17 transactions, I want the age & grouping to reflect the age of the customer as of the purchase date.

Customer 1: 20 and grouping 1-20

Customer 2: 53 and grouping 41-60

Customer 3: 2 and grouping of 1-20

What is the best method to get these results?

I've done something similar for groupings before by creating a blank query adding M code
(vals) =>
let ValBucket =
    {
        {(x)=>x<21, "1-20"},
        {(x)=>x<41, "21-40"},
        {(x)=>x<61, "41-60"},
        {(x)=>true, "61+"}
    },
    Result = List.First(List.Select(ValBucket, each _{0}(vals))){1}
in
    Result

and invoking a custom function but that was using a column with non-calculated data. If I try to enter a new column to get the age in years Age = INT ( YEARFRAC ( Patient[DOB], TODAY () ) ) then try to create a custom function it doesn't seem to be working correctly and does not give me the option to select my age column...I assume that's because it's calculated off the DAX? This has made my head spin so much that I have not even tried getting the age "as of" date, figured I'd just add this question here for assistance. I'm a complete novice and have NO CLUE what I'm doing so any help with be GREATLY appreciated! 

[v-juanli-msft · Accepted solution]

Hi @TabathaN

Assume your table is like

First create a relationship between "patient" and "transactional" tables based on "customer" column

Then create measures in the "transactional" table

Measure = DATEDIFF(MAX(patient[dob]),MAX([purcase date1]),YEAR)

Measure 2 = SWITCH(TRUE(),[Measure]<21,"1-20",[Measure]<41,"21-40",[Measure]<61,"41-60","61+")

when the purchase date change

Best Regards

Maggie

[TabathaN]

Ah yes! Thank you @v-juanli-msft! I understand now the logic you're using but could you please share the measures you created for this?

[v-juanli-msft]

Hi @TabathaN

Update!

[Ashish_Mathur]

Hi,

So, we have to first compute the date of first purchase for each buyer and then take the difference between this date and the DoB.  Thereafter, bucketing the age of buyers into buckets should not be a problem.

Share the link from where i can download your PBI file.

[TabathaN]

Unfortunately, I cannot easily get you a copy of my data because I am dealing with HIPAA protected data of medical patients. My example I provided still applies though. In real-life customers = patients and purchase = services rendered.

I have a table with patient data and a table with transactional data. So are you saying I would need to enter a formula in my transactional table to calculate on each row of transactions back to my patient table (DOB) to get age for each transaction then we can create buckets?
If so, how would you go about creating the buckets because my method of getting bucket data in just my patient table based off of DOB and today's date isn't working.

[Ashish_Mathur]

Hi,

Since 1 patient can be admitted multiple times, we will need to first compute the "Date of first admission" of each patient in one column.  In another column, we will compute the age of the patient by subtracting today's date and the Date of first admission.  We can later get the bucketing as well.

I do not need actual data - just a sample would do.  On that sample, please defeinitely show your expected result as well.