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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
dcare1010
New Member

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

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)

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!

 

 

2 REPLIES 2
amitchandak
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

 

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()) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.