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

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.] )
                    )
            )
    )
)

     

    • Gazsim44's avatar
      Gazsim44
      Helper III
      6 years ago

      Many thanks! 🙂

  • Gazsim44's avatar
    Gazsim44
    Helper III
    6 years ago

    Hi George, 

     

    Thanks but doesnt seems to be quite right in terms of the results. Let me give you a live example based on this calculation;

     

    Many thanks

     

    Invoice No.Invoice Date.Job No.Order (based on calculated column) Correct Order
    5920268208/11/19802690xxxx33
    5214277807/11/19802690xxxx11
    5214301408/11/19802690xxxx22
    5214812305/12/19802690xxxx34
    5920318105/12/19802690xxxx55

     

    • judspud's avatar
      judspud
      Solution Supplier
      6 years ago

      Hi Gazsim44 

       

      This appears to be an issue with the invoice numbers as the invoice number is higher but the invoice date is before.

       

      What logic do you have around the invoice numbers for the different types? i.e. credit notes etc

       

      Thanks,

      George