topic Amount of sales in tbl_A after the delivery date of a product from tbl_B in DAX Commands and Tips

Amount of sales in tbl_A after the delivery date of a product from tbl_B

Ttaylor9870 — Thu, 14 Dec 2023 16:14:16 GMT

Hey All!

I'm stuck with the following problem. I'm trying to see what the Sales for customers are in tbl_A before & after the customer recieved a promo product. 🙂

tbl_A contains the order_received_date & customer_id.

tbl_B contains the promo_sent_date & customer_id.

2 fact tables (tbl_A & tbl_B) linked by date & customer dimension tables. (Star Schema).

Many Thanks,

Taylor

Re: Amount of sales in tbl_A after the delivery date of a product from tbl_B

Tom_Y — Thu, 14 Dec 2023 21:28:19 GMT

Combine that 2 tables, so the new table will have the following columns:

Client ID/ Sales Date/ Sales amount/ Promotion date

Add a Custom column, and some simple code like (if Sales Date > promotion date, then AFTER, else then BEFORE). I'm not good at coding, hope you understand what I mean.

And then you can use the Maxtrix Visual to sort out BEFORE/ AFTER easily.

Re: Amount of sales in tbl_A after the delivery date of a product from tbl_B

Anonymous — Thu, 21 Dec 2023 08:38:17 GMT

Hi Ttaylor9870 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create a calculated column.

Column = var _date=CALCULATE(MAX('tbl_B'[promo_sent_date]),FILTER('tbl_B',[customer_id] = EARLIER('tbl_A'[customer_id]))) var _sales=CALCULATE(MAX('tbl_B'[sales]),FILTER('tbl_B',[customer_id] = EARLIER('tbl_A'[customer_id]))) RETURN IF([order_received_date]>=_date,_sales,[sales])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.