Calculate Totals per Keyword

Anonymous

Try this calculated column

Column =
IF (
    ISBLANK ( 'Table Invoices'[Keyword] ),
    CONCATENATEX (
        TOPN (
            1,
            FILTER (
                'Table Invoices',
                'Table Invoices'[Customernumber] = EARLIER ( 'Table Invoices'[Customernumber] )
                    && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
                    && 'Table Invoices'[Invoicedate] < EARLIER ( 'Table Invoices'[Invoicedate] )
            ),
            [Invoicedate], DESC
        ),
        'Table Invoices'[Keyword],
        ", "
    ),
    'Table Invoices'[Keyword]
)

Anonymous

Sorry for late reply.

It works like this

1) The Filter part filters the table and grabs only rows with a) same customer number b) non blank key words c) where date is before the current row date

FILTER (
                'Table Invoices',
                'Table Invoices'[Customernumber] = EARLIER ( 'Table Invoices'[Customernumber] )
                    && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
                    && 'Table Invoices'[Invoicedate] < EARLIER ( 'Table Invoices'[Invoicedate] )
            )

2) TOPN filters this (already filtered) table further and grabs the row with the maximum date

3) from above steps we have a single row of all the Columns in the Table...But we need only "Key Word" column. So I used an ITERATOR to grab it...We could also have used other ITERATORS like MINX or MAXX.....or other techniques like selectcolumns as well

Thank you very much, Zubair_Muhammad, it clear things up for me. Thumbs up, :-)