Calculated Column for New Customer

[mkleifgen]

Hey everyone,

I am trying to create a calculated column that will return the document date of that particular sales line item IF this customer hasnt placed an order within the last 12 months. This constitutes a new account for us.

Opening Order = IF(CALCULATE(MAX(v_PBI_SalesData[Document Date]), FILTER(v_PBI_SalesData, v_PBI_SalesData[Customer Number] = EARLIER(v_PBI_SalesData[Customer Number]) && v_PBI_SalesData[SOP Number] < EARLIER(v_PBI_SalesData[SOP Number])) IN(DATESBETWEEN(v_PBI_SalesData[Document Date], v_PBI_SalesData[Document Date]-365, v_PBI_SalesData[Document Date]))), BLANK(), v_PBI_SalesData[Document Date])

The error that this currently has is: A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

This is the calculated column I have created, but can't quite get it to work. My logic is...

Return the document date of this sales line if the same customer number, while being an earlier order, IF the last order they placed wasn't within the last 12 months of this line item.

Let me know if this doesn't make sense.

-Mark

[mkleifgen · Accepted solution]

I figured out how to code my calculated column. I've split the DAX command into two calculated columns..

1.  Previous Order = CALCULATE(MAX(v_PBI_SalesData[Document Date]), FILTER(v_PBI_SalesData, v_PBI_SalesData[Customer Number] = EARLIER(v_PBI_SalesData[Customer Number]) && v_PBI_SalesData[SOP Number] < EARLIER(v_PBI_SalesData[SOP Number])))

This calculated column is simply returning the date of the previous order for this customer.

2. Opening Order = IF(v_PBI_SalesData[Previous Order]>v_PBI_SalesData[Document Date]-365, BLANK(), v_PBI_SalesData[Document Date])

The follow up calculated column is now returning a blank if the previous order to this order has been within the last year, OR returning the document date of the line if it is before 1 year ago.

[Anonymous]

You are trying to combine FILTER() with IN() , thats why you are getting the error. If you could post some sample data then we can derive the formula for you.

Thanks

Raj