Hi all,
I've a model with the following entities:
- Customer DIM, including customer acquisition date
- Sales Fact, with all the purchases made by the Customers
- Calendar DIM, that is linked to Sales Fact.
On the Customer DIM (which is not directly join to Calendar) I created a dynamic column reporting the year of acquisition for each customer: Year of acquisition = YEAR(CUSTOMERS[CREATION_DATE].[Date])
In the report page I put a filter object allowing to select the Year from the Calendar. I configured the filter for only allowing single item selection.
What I want to do is to calculate dynamically on Customer level a label telling me about the state of the Customer in that year: New Customer (if the Customer was created in that year) or Existing. I would like to use the filter to compare sales made by New and Old Customers. Of course, the same Customer could have a different label based on the applied filters.
I tried creating a measure like this for giving me the selected year:
Selected year = IF(HASONEVALUE(CALENDAR[YEAR]);VALUES(CALENDAR[YEAR]);YEAR(TODAY()))
and tried to create a measure in the Customer DIM like this:
Label = IF(CUSTOMERS[Year of acquisition]=[Anno selezionato];"NEW CUSTOMER";"EXISTING")
But this doesn't give me the expected result.. Do you have any idea on what to do?
Thank you in advance for your help.
Francesca
