Link between two tables

[Anonymous]

Hello, 

I'm starting on Power BI and then I get to a more complex problem: 

I have two tables [Promotion] and [Invoices].

Promotion] is like this: 

The [Invoice] table is like this: 

By linking them with the promotion number I can display the total amount of purchases made under the promotion. 

However, I can't calculate the sales (from the invoice table) of the FA11 and FA12 product for the previous period of this promotion (the dates are in the promotion table) in the banners in which the promotion is applicable (here 1 and 2).

How do I make sure that when I filter the promotion code (P0102) I can have in addition to the sales made with the promotion code, the sales of these products (or the promotion applies) in these banners (or the promotion applies) for the previous period? 

In this case, I would like to have as a result: 

Promotion sales: 12$.

Previous period promotion $24.40

[v-jingzhang]

Hi @Anonymous 
I'm sorry I cannot understand your requirement clearly and I have several questions first.
Q1: When calculating the sum of sales, do we need to first determine which period it is in by comparing the Date column in Invoice table to the Start/End columns and Previous period start/previous period end columns?
Q2: I see the blank values in Promotion column of Invoice table. How to know which Promotion it is linked to or it just has no promotion?
Q3: In Promotion table, do previous period start/previous period end columns have the same values in Start/End columns? Just like below. And does the previous period also has a previous period to it?
 

The main difficulty I have now is how to determine which period the promotion of each product is in.

Best regards,

Jing