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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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