Forum Discussion
Solved
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 co...
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.
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!!