Additional filter to restrict data to pull from a certain column

[bvanderwatt]

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]

) ))

 

[TheoC]

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]

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