cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TomasPac Frequent Visitor
Frequent Visitor

Creating a calendar link between sales and purchases

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..

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating a calendar link between sales and purchases

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Creating a calendar link between sales and purchases

HI @TomasPac ,

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

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Creating a calendar link between sales and purchases

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.