dax measure to calculate age

[Peter083]

I have a Customer Table with the customers date of birth and this table is linked to a Transaction Table through the Customers Acc No. In the Transactions Table I have 15 years of data. I am trying to analyse the value of the transactions by the age of the customer in each of the 15 years. I am also trying to create age bands for these customers to create a pivot table that shows the age bands of the customers in the rows and the years in the columns.

Can anyone please help with the dax code to create such measures or if you can point me to an article describing how to do this, it will be greatly appreciated.

Thanks


Peter

 

[Matt Allington]

If you want to slice the data based on age bands, you must use a calculated column. But if you use a calc column, you cannot dynamically adjust the column based on the pivot table selection. Read my blog post on this topic here Banding in DAX - Excelerator BI I would suggest you create an age band in your fact table (not the customer table), as doing it this way will allow you to dynamically track the different ages fo the customers over the 15 years. You can access their DOB using RELATED(Customer[DOB]) from the fact table

 

[Peter083]

Makes sense, Thanks Matt