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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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()) )

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.