years between dates + categories using measures only

[Theo_]

Hi There,

I have been searching the forum but I can't find any posts that tackle my specific issue. I am building reports for a renting company and I and can only make measures, not calculated colums or new tables. 

I have the following two dimensions connected through Customer_ID:

Customer_ID	BirthDate
1	1-1-2006
2	3-6-1970
3	24-5-1998
4	4-9-2008
5	12-2-2023

 and 

Order_ID	Customer_ID	Rent_StartDate	Rent_EndDate
1	1	7-4-2023	21-6-2023
2	2	12-4-2023	22-6-2023
3	3	1-5-2023
4	4	8-5-2023
5	5	23-5-2023

What I am trying to do is two things: 1) calculate the customer's age in years at the start of the renting period and 2) organise these years in age-categories. With only measures. 

Just for context: I am already reporting the total orders (per month, based on Rent_StartDate) and I want to add the average age at the start of the rent, and the specific age groups at the start of the rent. 

So something like this:

	April	May
# Orders	2	3
avgAgeCustomer
# Customers 16-20
# Customers 21-25
# Customers 26-30
# Customers >30

Anyone here have an idea on how to tackle this issue?

Thanks!!

[Sahir_Maharaj · Accepted solution]

Hello @Theo_,

Can you please try this:

1. Measure to calculate the customer's age at the start of the renting period.

Customer Age =
DATEDIFF(
    MAX('Rent'[Rent_StartDate]),
    LOOKUPVALUE('Customer'[BirthDate], 'Customer'[Customer_ID], MAX('Rent'[Customer_ID])),
    YEAR
)

2. Measure to categorize the ages into different age groups.

Age Category =
SWITCH(
    TRUE(),
    [Customer Age] >= 16 && [Customer Age] <= 20, "# Customers 16-20",
    [Customer Age] >= 21 && [Customer Age] <= 25, "# Customers 21-25",
    [Customer Age] >= 26 && [Customer Age] <= 30, "# Customers 26-30",
    [Customer Age] > 30, "# Customers >30",
    BLANK()
)

3. Total orders, average age, and count of customers per age category.

# Orders = COUNTROWS('Orders')

avgAgeCustomer = AVERAGE('Rent'[Customer Age])

# Customers 16-20 = CALCULATE(COUNTROWS('Rent'), [Age Category] = "# Customers 16-20")
# Customers 21-25 = CALCULATE(COUNTROWS('Rent'), [Age Category] = "# Customers 21-25")
# Customers 26-30 = CALCULATE(COUNTROWS('Rent'), [Age Category] = "# Customers 26-30")
# Customers >30 = CALCULATE(COUNTROWS('Rent'), [Age Category] = "# Customers >30")

Do not hesitate to let me know if you might need further assistance.

[Theo_]

It works, thanks!!