cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Link between two tables

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: 

Christelle_0-1603745721840.png

 

The [Invoice] table is like this: 

Christelle_1-1603745748564.png

 

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

 

 

1 REPLY 1
Highlighted
Microsoft
Microsoft

Hi @Christelle 
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?
10283.jpg 

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

 

Best regards,

Jing

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors