Sum data by dates from 2 different tables

[SuperSayan]

Hi Everyone,

I'm trying to sum data from 2 different tables. Here is a simplification of the data:

Table 1:

- Column 1: date of sale

- Column 2 : price of sale

A lot of other columns

Table 2:

- Column 1 : date of another type of sales

- Column 2 : price

Few other columns.

I don't want to append both table because I have a lot of visuals based on table 1 and I don't want to add rows without all the columns as it will mess up my visualisation.

My goal is to be able to sum all the prices from both table in a measure (that's the easy part) and being able to visualise by month or year. The issue being both table have their own date reference.

I'm not sure this is very clear, don't hesitate if you need more info.

[Greg_Deckler]

Are these tables related to one another?

[SuperSayan]

@Greg_Deckler, they are not related. However they have a common  attribute (salesman). This value is not unique in any of those table but I have a salesman table so I could link both table through this salesman table.

Would that help?

[Greg_Deckler]

Yes, if you link the tables then you could theoretically use the date column from either one of them, just depends on how you want to slice it.

[SuperSayan]

I have linked the table in the below manner:

Table1[Salesman]    n-->1     SalesmanTable[Salesman]   1<--n      Table2[Salesman]

However, when I try to visualise my measure in an histogram by month, I get the same value for each month which is a clear indication that PowerBI doesn't know how to manage the date slicer.

Mesure is more or less the below:

SUMX(Table1, Table1[Price])+SUMX(Table2, Table2[Price])

[v-chuncz-msft · Accepted solution]

@SuperSayan,

Create a calendar table and build relationship on date column with your existing tables.

[SuperSayan]

Hello @v-chuncz-msft,

I was a bit reluctant to go down that road but at this point it looked like a good way out.

So I did that and I got a very surprising behaviour.

I am using one date from a table in different measure with the year info (example below), sorry my Power BI is in French...

But the error message is not very complicated, it is telling me that the date in my table (that I linked to the calendar table) has no "Year" attribute... I confirm my data is Date type (dd/mm/yyyy format).

réalisé 2017 = COUNTROWS(FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Year]=2017))

PS: As soon as I remove the relationship on the date between my content table and the calendar table, the measure is working again...

[v-chuncz-msft]

@SuperSayan,

You got the point. Use YEAR Function instead.

[SuperSayan]

@v-chuncz-msft

Works fine, don't know why it changes the behaviour or why I didn't think about that workaround myself.
Thanks!