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