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 called "Inv+Order." 

 

Based on the below formula, it's including my budgeted quanties. 

 

Any help will be much appreciated. 

TheoC 

 

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

 

 

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • 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] ) ) )