Forum Discussion

murali5431's avatar
murali5431
Icon for Helper III rankHelper III
6 years ago
Solved

Need to pull date based on part number

Hi,

 

I am trying to extract date based on initial entry date of each PN using below DAX formula.

 

Adj Entry Date = CALCULATE(MIN('Table'[Initial Entry Dt]),ALLEXCEPT('Table','Table'[Entry No],'Table'[Part Number]) )
 
Output is as shown below under Adj Entry Date field. I need to get 05-Jun-20 wherever it has 05-Jun-20 under Initial Entry Dt. This is a sample data and I have data with multiple entries rach having its set of Part Numbers. Please assist.
 
Entry NoPart NumberInitial Entry DtAdj Entry Date
ABC36389906-May-2006-May-20
ABC35795606-May-2006-May-20
ABC36389905-Jun-2006-May-20
ABC36389905-Jun-2006-May-20
ABC35795605-Jun-2006-May-20
ABC35795605-Jun-2006-May-20
ABC32711105-Jun-2006-May-20
ABC32711105-Jun-2006-May-20
ABC25547906-May-2006-May-20

 

Thank you,

Muralidhar

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi murali5431 ,

     

     

    Entry No Part Number Initial Entry Dt Adj Entry Date
    ABC 363899 06-May-20 06-May-20
    ABC 357956 06-May-20 06-May-20
    ABC 363899 05-Jun-20 06-May-20
    ABC 363899 05-Jun-20 06-May-20
    ABC 357956 05-Jun-20 06-May-20
    ABC 357956 05-Jun-20 06-May-20
    ABC 327111 05-Jun-20 06-May-20
    ABC 327111 05-Jun-20 06-May-20
    ABC 255479 06-May-20 06-May-20
    BBC 363899 01-Jun-19 02-Feb-19
    BBC 357956 02-Feb-19 02-Feb-19
    BBC 363899 01-Jun-19 02-Feb-19
    BBC 363899 01-Jun-19 02-Feb-19
    BBC 357956 01-Jun-19 02-Feb-19
    BBC 357956 01-Jun-19 02-Feb-19
    BBC 327111 01-Jun-19 02-Feb-19
    BBC 327111 01-Jun-19 02-Feb-19
    BBC 255479 02-Feb-19 02-Feb-19

     

    Is the column "Adj Entry Date" what you want? If it is, create a column like so:

    Adj Entry Date 2 = 
CALCULATE (
    MIN ( 'Table'[Initial Entry Dt] ),
    ALLEXCEPT ( 'Table', 'Table'[Entry No] )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Adj EntryDate = 
var partnum = SELECTEDVALUE(Table33[Part Number],Table33[Part Number])
RETURN
CALCULATE(MIN(Table33[Initial Entry Dt]),FILTER(ALL(Table33),Table33[Part Number]=partnum))
    • murali5431's avatar
      murali5431
      Icon for Helper III rankHelper III
      6 years ago

      Hi FarhanAhmed ,

       

      Thanks for the response!

       

      As I mentioned earlier, my data has multiple entries with same part number duplicating over the entries with varying initial entry dates. Data will be like

      Entry NoPart NumberInitial Entry DtAdj Entry Date
      ABC36389906-May-2006-May-20
      ABC35795606-May-2006-May-20
      ABC36389905-Jun-2006-May-20
      ABC36389905-Jun-2006-May-20
      ABC35795605-Jun-2006-May-20
      ABC35795605-Jun-2006-May-20
      ABC32711105-Jun-2006-May-20
      ABC32711105-Jun-2006-May-20
      ABC25547906-May-2006-May-20
      BBC36389901-Jun-1902-Feb-19
      BBC35795602-Feb-1902-Feb-19
      BBC36389901-Jun-1902-Feb-19
      BBC36389901-Jun-1902-Feb-19
      BBC35795601-Jun-1902-Feb-19
      BBC35795601-Jun-1902-Feb-19
      BBC32711101-Jun-1902-Feb-19
      BBC32711101-Jun-1902-Feb-19
      BBC25547902-Feb-1902-Feb-19

       

      Thanks for your help!

       

      Regards,

      Muralidhar

       

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi murali5431 ,

         

         

        Entry No Part Number Initial Entry Dt Adj Entry Date
        ABC 363899 06-May-20 06-May-20
        ABC 357956 06-May-20 06-May-20
        ABC 363899 05-Jun-20 06-May-20
        ABC 363899 05-Jun-20 06-May-20
        ABC 357956 05-Jun-20 06-May-20
        ABC 357956 05-Jun-20 06-May-20
        ABC 327111 05-Jun-20 06-May-20
        ABC 327111 05-Jun-20 06-May-20
        ABC 255479 06-May-20 06-May-20
        BBC 363899 01-Jun-19 02-Feb-19
        BBC 357956 02-Feb-19 02-Feb-19
        BBC 363899 01-Jun-19 02-Feb-19
        BBC 363899 01-Jun-19 02-Feb-19
        BBC 357956 01-Jun-19 02-Feb-19
        BBC 357956 01-Jun-19 02-Feb-19
        BBC 327111 01-Jun-19 02-Feb-19
        BBC 327111 01-Jun-19 02-Feb-19
        BBC 255479 02-Feb-19 02-Feb-19

         

        Is the column "Adj Entry Date" what you want? If it is, create a column like so:

        Adj Entry Date 2 = 
CALCULATE (
    MIN ( 'Table'[Initial Entry Dt] ),
    ALLEXCEPT ( 'Table', 'Table'[Entry No] )
)

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    murali5431 , the formula seems correct for part level

    see if this what you need

    Adj Entry Date = CALCULATE(MIN('Table'[Initial Entry Dt]),ALLEXCEPT('Table','Table'[Entry No] ))

     

    Hope this you are using it as measure not column