Add 'Order Raised' column

[Gazsim44]

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
12345678	666788	01/01/2020	1
12345678	666789	02/02/2020	2
98765432	555521	03/02/2020	1
98765432	555525	03/02/2020	3
98765432	555522	03/02/2020	2
10111213	222635	14/01/2020	2
10111213	222634	13/01/2020	1

[judspud]

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

[Anonymous]

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]

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]

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 · Accepted solution]

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]

Many thanks! 🙂

[Gazsim44]

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
59202682	08/11/19	802690xxxx	3	3
52142778	07/11/19	802690xxxx	1	1
52143014	08/11/19	802690xxxx	2	2
52148123	05/12/19	802690xxxx	3	4
59203181	05/12/19	802690xxxx	5	5

[judspud]

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

[Pragati11]

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]

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