Creating a calendar link between sales and purchases

[TomasPac]

Hello, I have three tables on SQL server:

1. Sales 

2. Purchases

3. Product ID table

Now I want to make a chart that would show Sales vs Purchases through years on axis (from 2015, till today). Sale and purchase table has creation date column for every entry that has been created, but those dates are diferent from each other. Also sales and purchases are linked through their product id to the product ID table. Main question, how can I show sales and purchases on the same chart and compare them by years/months. I tried to create a custom calendar table but I can not create good linking between them..

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the Date column of the Sales and Purchase Table to the Date column of the Calendar Table.  In your Calendar Table, extract Year and Month by using these calculated column formulas: Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  To your visual, drag Year and Month from the Calendar Table.

[v-shex-msft]

HI @TomasPac ,

Can you please share some sample data tables with same data structure?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng