Custom Dates using DATESBETWEEN

[Jlhoffner]

I have an analysis of new customers by channel (on rows) and Year (on columns). It shows for instance that 13,782 customers from Channel A have a first order date in 2008. Simple enough. What I want to do is create a measure that counts the numbers over the 365 days AFTER their first order date (which I have as a field on DIM_Customer, as well as the first order flagged as TRUE on the FACT_Orders table). So I in essence it seems like I need to create a 365 context for each individual customer since each of their first order dates is unique to the customer.

If I were doing this in a database, I would isolate the cust ID's of the 13,782 customers and sum their orders on the order table for the time period in question via a join on cust ID.

Feels like I could use DATESBETWEEN in DAX/Powerpivot, but I can't figure out how to get that range to be the next 365 days after that first order date.

Can anyone help?

Jude

 

[MD610]

This is a little hard to follow without some sample data. Do you have multiple date fields in the model?

Time Intelligence Functions in PowerPivot require a separate date table of unique dates related back to your date fields to work properly.

If you have that then this shouldn't be too hard using some combination of DATESBETWEEN or DATEADD. Maybe even FILTER could be used depending on your data model.

Can you provide any samples? Otherwise, I'm not sure there is a enough to go on here to come up with a valid solution.

 

[Jlhoffner]

Hi Mike, I do have samples I can provide but I'm new to this forum so I'm not sure what's easiest. I have PDF's of the report I'm trying to replicate and can share the data model or measures I've written if you want. I have a separate Date table and it's related back to the Orders table. I think all the pieces are in place I'm just struggling with the right combo of Time functions and their syntax. Any help is greatly appreciated. Let me know how I can share exhibits.

Thanks Mike.

 

[MD610]

If you can just post on here the name of each table in your model and the relevant column names in them, I should be able to assist.

Also, for clarification, is the measure you are trying to reach simply a count of orders that came in during the customers 1st year?

 

[Jlhoffner]

Thanks so much Mike, here goes.

First, I'll mention that I've created two versions of my model since I'm using this as an opportunity to educate myself. The difference between the two models at the moment is the mix of active/inactive relationships. I've done that to play with USERELATIONSHIP to leverage an inactive join, etc and see if I can solve the same problem two different ways. So if you have any reco's on best practice for the long term structure of this I'm all ears. For instance, keep the relationship between Orders(order date) and Calendar active and between Customer (first order date) and calendar inactive, or vice versa. Ok, very simple table list...

Table 1 = Customer
Cust Num
First Order Date
Last Order Date
First Order Channel
Source of First Order (link to a Source Code table that I haven't brought in yet)

Table 2 = Orders
Order Date
Order Num
Source Code
Cust Num
Order Channel
Demand
Cost of Goods Sold
Profit (calculated column I created, simple Demand-COGS)
Is First Order Flag (TRUE/FALSE) - This is redundant with First Order Date on Customer, but I created it for playing around per above)

Table 3 = Calendar
DateKey
Year
Month Name
Month Num of Year
Day Name
Day Num of Week
Day Num of Month
Quarter
Quarter/Year
Month/Year
etc.

The answer to your question is Yes. If I do a count of customers by Year (columns) and First Order Channel (Rows) by First Order Date, I get a result that says, for instance, that there were 13,782 customer with a first order date in 2008 and first Order Channel of "A". I want a count of orders that came in from those 13,782 in their first year, excluding that first order, so I can easily compute Orders/Customer in First 12 Months after the first order. I would ultimately extend this to the count in the period 13-24 after the first order, excluding the 0-12 month period. Further, I'm assuming I can apply this same approach to other key metrics like Demand, Profit, etc for which I have already created simple base measures (e.g. - Demand = sum(Orders[Demand])

Clear as mud?!

Jude

 

[MD610]

As is often the case, there are several ways to handle this. Like you mentioned, you could relate all date fields to a single Calendar table and then navigate everything by USERELATIONSHIP. This isn't my preferred option because it adds extra complication to every measure you write.

You could also use multiple Calendar tables, where each date field gets related to its own Calendar. I prefer this over method one but still try to avoid it if I don't need it.

I think the easiest solution from what I understand about your data is to only relate your Calendar table to Order Date.

Then with 2 relatively simple Calculated Columns in your Order table, you should be able to write most of the measures you need.

The first Calculated Column is just to check if the order is the First Order:

Orders[FirstOrder]=[Order Date]=RELATED(Customer[First Order Date])

The second Caluculated Column is to check if the order is within 365 days of the first order:

Orders[365]=[Order Date]>related(Customer[First Order Date]) && [Order Date]<=related(Customer[First Order Date])+365

Now you have 2 TRUE/FALSE Columns that can be used as the filter arguement in a CALCULATE to modify any measure.

So if you wanted the Order Count of the first orders in each year, you could use a measure like:

[FirstOrderCnt]=CALCULATE(COUNT(Orders[Order Num]), Orders[FirstOrder] = TRUE())

You could substitute any of the other measures you have created for the first arguement in CALCULATE and you can change the column to Orders[365] in the second arguement to get a measure for the first 365 days after the first order.

Hope this helps.

 

[Jlhoffner]

Mike, I will try this. It makes sense to me and hopefully I'll get the same results as the report I'm trying to replicate. Will let you know. But I couldn't proceed without first thanking you profusely for your thoughtfulness and help!

Jude