I have a list of customer ids, revenue amounts and revenue dates. I'd like to sum revenue per customer over a fixed date range (let's say the first 30 days following their first transaction) and use this calculation in views with longer date ranges.
Is there a way to do this with level of detail calculations or something else?
Hello Kieran - What you are looking for is the Customer level first 30 days (Parameterised) of Sales transactions. For that you need to follow three simple steps :
Step 1: Create a calculated field called
"First Purchase date" and the calculated expression would be {FIXED [Customer Id]: MIN([Order Date])}
This will return first purchase date for each customer
Step 2: Create a parameter called "Date Range" of type Integer and default value as 30: This will allow to receive input from user. This value can be changed from the dashboard.
Step 3: Create another Calculated field called "Customer Revenue" and that should contain the following code
if [Order Date]>= [First Purchase date]
AND [Order Date]<= DATEADD('day',[Date Range],[First Purchase date])
THEN [Revenue]
END
This will provide revenue for the Customer for first 30 (based on the parameter input) days of Revenue.
Now use this field "Customer Revenue" and "Order Date" to create any type of Visualization.
Thanks,
Manideep
