Forum Discussion
years between dates + categories using measures only
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!!
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.
2 Replies
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.
It works, thanks!!
