Help to Join diferents dates tables

[JoseDavid]

Hello everybody

I need to calculate the difference between the sales and the forecast.

The problem it´s each table has his own dates, like that:

and

When I make a formula or a grafh, one of dates must order above the another.

Somebody can help me?

Thanks very much

jd

[Thejeswar]

Hi @JoseDavid,

If you mean that the data of the same dates should sum yp each other, then you should try appending the tables into a single table.

Otherwise, join the tables based on a common date column and use that date column for the visual

[JoseDavid]

"Otherwise, join the tables based on a common date column and use that date column for the visual"

Oks, can you say me how to do it?

Thanks!!!

[Thejeswar]

Hi @JoseDavid,

Appending the tables would be a better solution if you want to show a simgle bar for a date. But since you are asking for the 2nd solution, below comes it

As shown in the below image, have a new table that will have all the dates of the period for which you will need.

 

In this image, the Table1(2) will have all the dates in the period of my interest. Table1 and Table2 are the 2 tables that I have with a similar data

This Date table can be created using a calendar function in the Create Table Option in the Modelling Section of the Top Ribbon in Power BI Desktop

Date = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))

The Above Code gives all the dates between 1/1/2017 to 12/31/2018

Note: For joining these tables your Calendar table Date format and Source Table Date format should be same

[JoseDavid]

Hello Thejeswar

I have created the date table with the same format of each other.

But when i try to combinate the tables, it´s give me the following error:

"You can not create a relationship between these two columns because one of the columns must have unique values".

Then, I have tried combinate from the querys editor but i don´t find a formula wich combinate dates 1 and dates 2........ 😞

[v-huizhn-msft · Accepted solution]

Hi @JoseDavid,

As the error message said, the many to many relationship is unsupported in Power BI, one of the columns must have unique values. You should remove the dulplicate values and create relationship between two tables.

Thanks,
Angelia

[JoseDavid]

Thank you very much!!!!, @v-huizhn-msft

jd

[v-huizhn-msft]

Hi @JoseDavid,

I am glad to help you, please mark the reply as answer if it is useful.

Thanks,
Angelia

[JoseDavid]

What do you mean with "Appending the tables"?

Do you think it´s the best solution? Can you help me telling me how to do it?

Thanks!!!!!!!!!!! very much!!!!!!!!!!!!!!

jd

[Stachu]

you need to create separate Date only table that joins to both the Bill Date and Forecast Date, and use the new column in the visuals

[JoseDavid]

Yes, I was imagining something like that, but, Must I create the new table joining the another two dates tables, or I only need create a independent data table? I don´t now how to do it.....