Sum value from inactive relationship minus value from active relationship

[kasiaw29]

Hope this makes sense! 

I have a date dim and a table- lets call it sales. 

Sales table has an active relationship on sales date and an inactive relationship on refund date. 

I have a sum of sales and I also have a sum of refunds ( by using USERELATIONSHIP ). If I drop them on a chart where my axis is just date from date dim I get a perfect view of the value of sales and refunds (all refunds are negative values) by month. 

I've been asked to take away refunds from sales values to get a true sales value figure. 

Is this even possible? I did try taking simply one away from another but it did not work as expected. 

Examples: 

True Sales = [Sales Value] - [Refunded Values] 

Refund Values measure=

CALCULATE(

    SUM('Sales'[Total Gbp]),

    'Sales'[Transaction Code] =  "REFUND" ,

USERELATIONSHIP(DateDim[Date],'Sales'[Refunded Date]))

Thanks,

Kasia 

[amitchandak]

@kasiaw29 , Try like

CALCULATE(
SUM('Sales'[Total Gbp]),
filter('Sales','Sales'[Transaction Code] = "REFUND" && not(isblank('Sales'[Refunded Date]))),
USERELATIONSHIP(DateDim[Date],'Sales'[Refunded Date]))

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kasiaw29 · Accepted solution]

Hey @amitchandak  thanks for your solution. I looked at it with fresh pair of eyes today. I was making THE most silly mistake ever of doing this True Sales = [Sales Value] - [Refunded Values]  where it should have been True Sales = [Sales Value] + [Refunded Values] 

As refund values were negative so -1500, -500, -5478 I needed them added to sales value so that it would actually take them away. 

[v-kelly-msft]

Hi @kasiaw29 ，

Sorry I'm a little confused about your request,is your issue solved now?If not,could you pls share some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!