Forum Discussion
No Relations found between two tables
Hey everyone,
I am a Power Bi newbie and I have a very simple problem for you. Sorry for my bad englisch, i am not a native speaker.
I have two tables with 3 columns each. One table stands for one year. My first column is the date column, in the second column are the manufacturers and in the third column stands the sales volume. My goal is to establish a connection between these two tables to compare the sales amount per producer between the two years.
Table 1 (2015):
date description sales amount
Table 2 (2016):
date description sales amount
The Problem is, that Power Bi cant find a connection between the two tables. I grouped the manufacturer column an the data column, but that didnt work.
So what did I wrong ?
You should combine the data from both of these tables into a single data table either before load (via a query) or during load (via Power Query - Get Data). You should then have a single data table containing date, desc, sales amount.
Then create and load a calendar table (sounds like you may have one of these already). it needs to have the same granularity as the dates in your data table. eg if your data table dates are "day level", then your calendar table must be the same. Read my blog post about calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/
Assuming you have a day calendar table, you can use inbuilt time intelligence to solve the problem.
Total Sales = sum(data[Sales Amount])
Total Sales prior year = calculate([total sales],dateadd(calendar[date],-1,Year))
4 Replies
You should combine the data from both of these tables into a single data table either before load (via a query) or during load (via Power Query - Get Data). You should then have a single data table containing date, desc, sales amount.
Then create and load a calendar table (sounds like you may have one of these already). it needs to have the same granularity as the dates in your data table. eg if your data table dates are "day level", then your calendar table must be the same. Read my blog post about calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/
Assuming you have a day calendar table, you can use inbuilt time intelligence to solve the problem.
Total Sales = sum(data[Sales Amount])
Total Sales prior year = calculate([total sales],dateadd(calendar[date],-1,Year))
Thank you for your reply, it was very helpful.
I created the Calendar table und startet to create two measures, with the syntax you posted below.
But the last Syntax seemed to be wrong.
I tried two versions
The first one with the function Date (if you mean that with date)
.... calendar(DATE,-1.Year)).
Or a second version with a reference to my date table
... dateadd(CALENDAR('Date Table'[Date];-1,year))
But everytime Power BI said "wrong syntax for 'year'. (DAX(CALCULATE('XX'[Total Sales],dateadd(CALENDAR('Date Table'[Date],-1.year)))).
So what did i wrong ?
Tobias_Lehmann2 you're using periods where you should be using commas.
