Forum Discussion

Solution Specialist

3 years ago

hi

hi I have a sales table where i have value sold and return. When sold invoice number is normal a number but when return then there is CR at beginning and CR or return invoice number is not sam...

Need Help

abc_777

Solution Specialist

3 years ago

v-luwang-msft

thanks for your efforts. really great. would please help me find where there error is in my calculative column

invoice value that is fully refund with CR should be both be ) but mine not doing that. PLease help

test = IF('bm_retail_t ssummary'[NET_AMT] < 0,

'bm_retail_t ssummary'[NET_AMT] +

CALCULATE(

MAX(

'bm_retail_t ssummary'[NET_AMT]),

FILTER(ALL('bm_retail_t ssummary'),'bm_retail_t ssummary'[Index] =

EARLIER('bm_retail_t ssummary'[Index]) - 1)),

if(CALCULATE(

MAX('bm_retail_t ssummary'[NET_AMT]),

FILTER(ALL('bm_retail_t ssummary'),

'bm_retail_t ssummary'[Index] = EARLIER('bm_retail_t ssummary'[Index]) + 1)) < 0, 0, 'bm_retail_t ssummary'[NET_AMT]

)

abc_777

Solution Specialist

3 years ago

I think we made a mistaked

221104	120
CR999999	-120

not always be together. invoice number and corrorpondance invoice CR could be in different place, could be after fifteen thousand or tweenty thousand rows. could be anywhere in same table.

in that case - 1 is not right.

we could say when CR Nat Amount matches with invoice Net Amount and in same invoice date (I didnt mention it in my example table but you can create it in yours) then make CR Nat Amount and Net Amount of them 0

data is something like this its over 3 milions of data with invoice and CR Net amount and Invoice Date