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

     

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