Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-jingzhang
Community Support
Community Support

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?
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.