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

     

judspud's avatar
judspud
Solution Supplier
6 years ago

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