Forum Discussion

bvanderwatt's avatar
bvanderwatt
Icon for Helper III rankHelper III
4 years ago

Additional filter to restrict data to pull from a certain column

Good Day    I am using the below formula to identify the first date in a product is purchased.    However, I need to add another filter to restrict the data to only pull from a certain column cal...
Need Help
Show and Tell
Tips and Tricks
TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

Hi bvanderwatt 

 

You should be able to integrate ALLEXCEPT into your filters, something along the lines of:

 

FirstTransactionDate =

CALCULATE (
     FIRSTDATE ( 'Combined Sales'[InvoiceDate] ) ,
         FILTER ( ALLEXCEPT ( 'Combined Sales' , 'Combined Sales'[Inv+Order] ) , 'Combined Sales'[StockCode] = EARLIER ( 'Combined Sales'[StockCode] ) 
                 ) 
          )
 
Hopefully that helps!  Haven't tested syntax so keep me posted.
 
Theo 🙂
 
  • bvanderwatt's avatar
    bvanderwatt
    Icon for Helper III rankHelper III
    4 years ago

    Thank you so mcuh for helping me. I reaaaaally appreciate it. I've been struggling for about 5 hours since you came along 🙂 

     

    I've added the calculated column, but i dont understand why it's giving me different "first transaction" dates for the  exact same product. The formula is supposed to give me the first transaction date of when the product was sold. 

     

    Also, would it be possible to exclude a certain customer class called "exclude" from this formula. Sometimes I send samples and I've excluded those accounts in my customer class. 

     

     

    FirstTransactionDate =
    CALCULATE (
    FIRSTDATE ( 'Combined Sales'[InvoiceDate] ),
    FILTER (
    ALLEXCEPT (
    'Combined Sales' ,
    'Combined Sales'[Inv+Order] ) ,
    'Combined Sales'[StockCode] = EARLIER ( 'Combined Sales'[StockCode] ) ) )