Forum Discussion

Gazsim44's avatar
Gazsim44
Helper III
6 years ago
Solved

Add 'Order Raised' column

Hi, I have a report I am building which in basic form consists of a Job No, Invoice No and Invoice Date. There can be several invoice's on one job and then also several dates etc.

 

Is there a way I can add an additional column in either Power Query or as a calculated column to indicate in which order an invoice was raised per these three parameters? My table in essence would then look per below with the Order column being the additional added element.

 

Thanks

 

Job No.Invoice No.Invoice Date.Order Raised
1234567866678801/01/20201
1234567866678902/02/20202
9876543255552103/02/20201
9876543255552503/02/20203
9876543255552203/02/20202
1011121322263514/01/20202
1011121322263413/01/20201
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    To order on dates first, invoice number second:

     

    [Order Raised] =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Job No.] = EARLIER ( 'Table'[Job No.] )
            && (
                'Table'[Invoice Date.] < EARLIER ( 'Table'[Invoice Date.] )
                    || (
                        'Table'[Invoice Date.] = EARLIER ( 'Table'[Invoice Date.] )
                            && 'Table'[Invoice No.] <= EARLIER ( 'Table'[Invoice No.] )
                    )
            )
    )
)

     

10 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Gazsim44 ,

     

    Can you please elaborate on the logic you want your "Order Raised" column to be calculated?

    In the table screenshot, it is confusing. Why there is "1" appearing three times in "Order Raised" column?

     

    Thanks,

    Pragati

    • Gazsim44's avatar
      Gazsim44
      Helper III
      6 years ago

      Hi Pragati, 

       

      The logic should be that following the job number (being the unique identifier for each file) the next column to look at would be invoice date as naturally anything raised for example on 1st Feb comes before 2nd Feb and so on.

       

      To then be able to identify which invoice comes first if we have more than one raised on a single day we would then need to take into account the invoice number. These are issued in sequential invoice number so for example 111222 comes before 111223 and so on, 

       

      Hope this helps?

       

      Thanks

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    Calculated column should do the job. If the order is based on invoice number, something like this:

    [Order Raised] =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Job No.] = EARLIER ( 'Table'[Job No.] )
            && 'Table'[Invoice No.] <= EARLIER ( 'Table'[Invoice No.] )
    )
)

     

    • Gazsim44's avatar
      Gazsim44
      Helper III
      6 years ago

      Hi & many thanks, 

       

      This works fine but doesnt take into account the date? This does need to factored in before the invoice number as there are different sequences of numbers (for credit notes etc). 

       

      Sorry I should have stated this at the beginning, 

       

      Thanks again, 

      • judspud's avatar
        judspud
        Solution Supplier
        6 years ago

        Hi Gazsim44 

         

        Please see updated code to include the dates

         

        Order Raised = VAR job = Table[Job No.]
VAR InvoiceDate = Table[Invoice Date]
VAR invoice = Table[Invoice No.]
return
CALCULATE(COUNTROWS(Table),FILTER(all(Table),STable[Job No.]=job && Table[Invoice Date]<= InvoiceDate && Table[Invoice No.]<=invoice))

         

        Hope this helps

         

        Thanks,

        George

  • judspud's avatar
    judspud
    Solution Supplier
    6 years ago

    Hi Gazsim44 

     

    The following calculate column formula will work

     

    Order Raised = VAR job = Table[Job No.]
var invoice = Table[Invoice No.]
return
CALCULATE(COUNTROWS(Table),FILTER(all(Table),STable[Job No.]=job && Table[Invoice No.]<=invoice))

     

    Thanks,

    George