Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

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


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)

2023-01-11 20_29_51-Ventas Vadali BI - Power BI Desktop.png



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.


2023-01-11 20_41_21-Ventas Vadali BI - Power BI Desktop.png


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



Super User
Super User

@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 you can use a custom column

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

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?

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.