Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mkleifgen
Frequent Visitor

Calculated Column for New Customer

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

1 ACCEPTED SOLUTION
mkleifgen
Frequent Visitor

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.

View solution in original post

2 REPLIES 2
mkleifgen
Frequent Visitor

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.