Forum Discussion

mkleifgen's avatar
mkleifgen
Frequent Visitor
8 years ago
Solved

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. Thi...
  • mkleifgen's avatar
    8 years ago

    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.