How can i subtract a credit note value from a sales invoice and then have the credit note deleted

[twanahc]

Hi,

I have a dataset containing all our invoice numbers.

Each invoice number has a classification; a) Sale or b) Credit note (return of that sale or parts of it).

In the dataset contains the quantity that we sent an invoice for whether or not it is a sale(positive) or a credit note/return(negative).

Each credit note is in turn attached to a sale and the same on the other way around.

An example:
Sale 1001 is connected to Credit note 1002. - Quantity +10 on 04/01/2018

Credit note 1002 is connected to Sale 1001 - Quantity (-4,5) on 11/01/2018

I would like the credit note that is attached to the sale to be located, subtracted from the sale and then the credit note gets deleted thereafter.

Any idea on how i can do this?

[Anonymous]

@twanahc

Assuming you have a way (column) to identify the corresponding sale for a credit note, your table can look like below -

Invoice	Type	Amount	Corresponding to Sale
123	Sales	1000	123
124	Credit Note	-200	123
125	Sales	1200	125
126	Credit Note	-250	125
127	Sales	1000	127
128	Credit Note	-800	127
129	Sales	300	129
130	Credit Note	-300	129
131	Sales	200	131
132	Credit Note	-50	131

If so, you can create a measure as below - 

NetSales = CALCULATE(SUM(Sales_CN[Amount]),
ALLEXCEPT(Sales_CN,Sales_CN[Corresponding to Sale]) )

This will give you the Net Sales for each of the invoice and table visual can look like below...

Note that I have put in a page level filter to include only "Sales" (i.e. no credit notes)

[v-yuta-msft]

Hi twanahc ,

What's your data structure and expected result? Could you please clarify more details about your requirement?

Regards,

Jimmy Tao