Calculate total sales by weeks, including returns from customers

[DimaMD]

Hello community!

Need your help in solving one very important task.

The task is to calculate total sales amount in money and quantity by specific week that we choose in calendar. To do this we need to consider both sales and returns from customers

For this we have 2 tables:

1) "Sales products", with collums date, Customer, total sales, total quantity, Sales ID

2) "Return products", with colums date, customer, return ammount, return quantity, sales ID, return ID

The connection between 2 tables are made with sales ID and calendar date. We need to calculate how much money and quantity of products were sold in specific period by summarizing sales, but also we need to consider return table as refunds. So the total should be like "total sales-returns"

The problem is that in some cases we have (for example) salesID4 with data in week 1, but the return that is connected to this salesID4 is in week 2. When we choose week 2 in calendare we should have seen return amount and quantity, but we dont, as we dont have sales connected to this return in week 2.

Question is - how can I consider returns in specific week, even when there is no sales connected in this week?

Example pbix

[amitchandak]

@DimaMD , I such case I would prefer to do this on database side or power query( using merge and max for date) to bring in sales date into return table, Suggesting a new column in DAX

a new column =

Sales Date = maxx(filter('Sales product', 'Sales product'[Sales ID] ='Return'[Sales ID]), 'Sales product'[Date])

You can join this with date table or keep in active join to use userelation in measure

calculate(sum('Retuns'[Cost retun]), userelationship('Date'[Date], 'Return'[sales date])) //inactive join case

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[DimaMD]

Hi @amitchandak and thank you for quick reply.

Your solution works, but ther is one problem that we needed to solve from the beginning. If we use your formula it considers party affiliation of goods, but in our case we dont need to consider it.

For example if we use your formula and we have sale in week 1, return in week 2, then return will be used in calculation of week 1. We dont need this, in our case even when sale was in week 1 and return in week 2, we need to consider return only in calculation of week2

[amitchandak · Accepted solution]

@DimaMD , In that case, join of date table with date of return should do. But you should use date table in slicer and visual

[DimaMD]

@amitchandak thank you very much! Your solutions and advices really helped us!