Date relationships not working

[adrien5555]

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

here is a look at the two linked dates:

thanks for your help

[Anonymous]

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]

@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."

@Anonymous: "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,

[Anonymous]

@adrien5555 You are in power bi desktop right?

Regards

Rodrigo

[adrien5555]

@Anonymous: 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 :

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

[Anonymous · Accepted solution]

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

[adrien5555]

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

[Greg_Deckler]

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.