Getting the previous cost center information used for products in previous bookkeeping

[DataNinja777]

Hello Power BI community,

I'd like to prepare bookeeping journal entry by taking the previous cost center used in the negative amount for particular products.  The required output is the 4th column, and negative entries are the clearing entries used previously for particular product, and I'd like to bring in the calculated column, the cost center used previously for the negative amount bookkeeping for the product.  I'd greatly appreciate it if anyone could write the dax formula for me to get the 4th column result from the 1-3 columns' information below. 

Product	Amount	Cost center	Cost center output
A	220	US3	UK1
B	200	US4	UK3
C	300	US5	UK5
D	500	US6	UK8
E	700	US7	UK9
F	780	US9	UK9
E	700	US7	UK9
A	(220)	UK1	UK1
B	(200)	UK3	UK3
C	(300)	UK5	UK5
D	(500)	UK8	UK8
E	(700)	UK9	UK9
F	(780)	UK9	UK9

Thanks a lot for your help!

[DataNinja777]

Thank you!

[danextian · Accepted solution]

hI @DataNinja777 ,

Assuming that you want to include in the logic the amount, try this:

Prev Cost Center = 
VAR __POSITIVE_AMOUNT =
    CALCULATE (
        MAX ( 'Table'[Cost center] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Amount] = - EARLIER ( 'Table'[Amount] )
        )
    )
VAR __NEGATIVE_AMOUNT =
    CALCULATE (
        MAX ( 'Table'[Cost center] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Amount] = EARLIER ( 'Table'[Amount] )
        )
    )
RETURN
    IF ( 'Table'[Amount] < 0, __NEGATIVE_AMOUNT, __POSITIVE_AMOUNT )