we are a new team of data analysts using power bi and face the problem of finding and tracking new customers with two seperate date columns:
The customer aquisition date and the transaction date(s). Once a customer was aquired multiple transaction dates can occur.
Basically, we want to present new customers per month; their transactions/sales over the following months and compare them with total transactions, or new customers from previous/subsequent months.
Though we worked through the solutions presented in the tutorials regarding this topic, we could not figure out how to visualize customers per month. Especially, as they defined as not being customer on any date prior to the aquisition date, for our purposes.
In the end we want to show new customers per month with all their transactions to compare for different time periods:
current selection, sameperiodlastyear, last month
Looking forward to your suggestions. We really appreciate your help.
Masked Data model.pbix (1.4 MB)
(By masking the data initial progress was lost due to reduction in table dimensions)
I duplicated your datamodel table and renamed it DimCustomer. In the DimCustomer table I kept the Customer Name and the Customer Acquisition Date (removed duplicates - should only have one acquistion date although there appeared to be more than one).
In the Datamodel table, I created a conditional column, if transaction date was null use acquisition date otherwise use transaction date.
Created new relationship from Datamodel to DimCustomer for Customer Name.
Create KeyMeasure table and created a measure Sales Total
I haven’t tested the data but seems to be working ok. You may need a year filter but this should get you going. Masked Data model v1.pbix (1.7 MB)
Thank you for your fast reply. I think you found a solution for our problem. Though the transaction and the aquisition date can’t be used interchangably, I tested the solution with the actual data and it looks good. The rest will be filtering for year and month etc.
So thank you again, we really appreciate your solution.