How to create a Date relationship with a table with no dates?

[dcare1010]

Hello!

Im a bit new to this world and hope you all can help me 😃. Please take note that the model is in spanish so I will try to be clear as much as I can. ("Fecha" means "Date'')

As the title states, Im struggling quite a lot to create a relationship between dates of my Sales Table (''Ventas'') and another table(''Rentabilidad'') which doesnt contain dates  (the data in this table correspond to 12 different excel sheets, one for each month of the year 2022; I think the sales platform didnt find necessary to add a column with the date as each table is downloaded separately)

I have been researching a lot and found out that someone created a new column in Excel with the dates. So I created it for each month. For example for January sheet, created a new column which says 01-01-2022 and copied it for each row (150 rows approx). For February: 01-02-2022, and so on.

Then I imported the 12 sheets (folder) to Power Bi and created a dimension table ("Fecha 2") with the formula DISTINCT, and chose the Date values of the table that contains the new Date columns ( I did this because I followed up a tutorial which explained how to create a relationship between product Ids, thats why there is a middle table related with the upper and bottom table called "Códigos" which means "Code"; this relationship works fine).

After that, I created the relationship (Picture 1) . Then in the report window I chose a bar chart, added the new dimension table ''Fecha 2'' to the X Axis and it looks like this: (the date hierarchy dissapeared, even on the date column of the table that originally had dates.

When there is not relationship and only use data from the table that has dates, the chart looks like this: (date hiearchy is back)

Totally normal.

Obviously I dont have the relationship concept clear, I thought I had it because I indeed related the product ids from both tables, but the dates are not working.

I hope I made myself clear. Please let me know if my requirement is confusing and I will try to explain again. I can't upload the file due to confidentiality issues.

Thanks in advance!

[amitchandak]

@dcare1010 , if these sheet are from month year, first add month and year in power query and then create a date date using those

date= date([year], [month],1)

or

date(2022,[month],1)

https://community.powerbi.com/t5/Desktop/Excel-sheet-name-in-a-Column/td-p/2486541

https://community.powerbi.com/t5/Desktop/Add-custom-column-with-sheet-name/td-p/877507

Or you can use a custom column

last year end date = eomonth(today() , -1*month(Today()) )

[dcare1010]

Thanks for your reply. The new column should be added to both tables or just the one that doesnt have the dates? 

The format of the one that has dates is Day-Month-Year so I think that one is fine. So for the one that doesnt, I should add just the month?

What about the relationship I created? Is it alright?