cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adrien5555 Regular Visitor
Regular Visitor

Date relationships not working

Hi all,

 

Two different sources, linked by dates, but can't get a visual with 2 bars each month
(green bar ok, grey bar not ok):

 

test.png

 

here is a look at the two linked dates:

 

test4.png

 

 

 

test2.png

 

test3.png

 

thanks for your help

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
rockdrigom Regular Visitor
Regular Visitor

Re: Date relationships not working

You should use a Date table an create relationships to both tables. Then in the chart use the Month of the Dates table to see the amounts.

Date tables must contain continuous dates.

 

Regards

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: Date relationships not working

Looks like your dates are in 2 different formats, might try making them both mm/dd/yy or dd/mm/yy. Blanks generally indicate nont-matching rows in a relationship.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rockdrigom Regular Visitor
Regular Visitor

Re: Date relationships not working

You should create a new colum with something like STARTOFMONTH(IssuedAt) to get the same value in both tables.

Then make the relationship with the new column.

Regards 

Rodrigo

adrien5555 Regular Visitor
Regular Visitor

Re: Date relationships not working

@Greg_Deckler: same date type : dd/mm/yyyy. I tried with a solution you gave elsewhere like = MONTH([date]) & YEAR([date]) but I get 

"Expression error: The name 'MONTH' wasn't recognized. Make sure it's spelled correctly."

 

@rockdrigom: "get the same value in both tables." => sure, got it, thanks!

 

I'm just beginnig with PBI...I get the following error:

 

"Expression error: The name 'STARTOFMONTH' wasn't recognized. Make sure it's spelled correctly."

 

 

I think I don't enter DAX correctly...

 

best,

rockdrigom Regular Visitor
Regular Visitor

Re: Date relationships not working

@adrien5555 You are in power bi desktop right?

 

1.JPG

 

 

Regards

 

Rodrigo

adrien5555 Regular Visitor
Regular Visitor

Re: Date relationships not working

@rockdrigom: yes in PBI. But I was in the Query Editor 🙂

 

Actually, it's very unclear to me when to edit data in the Query Editor and when to do it in the modeling... 

 

Doing the formula in modeling works great, and i updated the relationship

 

It's getting much better :

 

test.png

 

but the grey bar doesnt display the good number for each month: either a total or average

 

 

Highlighted
rockdrigom Regular Visitor
Regular Visitor

Re: Date relationships not working

You should use a Date table an create relationships to both tables. Then in the chart use the Month of the Dates table to see the amounts.

Date tables must contain continuous dates.

 

Regards

View solution in original post

adrien5555 Regular Visitor
Regular Visitor

Re: Date relationships not working

@rockdrigomThanks! This is really a best practice I hadn't read so far... Microsoft should create a tutorial to cover this. I guess the date table and country table are probably the most used.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors