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