Help please my date table wont work

[Kieran_choudary]

Hi I am new to Power BI and DAX, so please bear with me,

I am trying to create a MOM and WOW and YOY function and came to understand i would need a date table to do so, 

i have tried many different ones and methods i found in forums but none seem to work for me,

PLease see the pictures below and see if you can figure out why

This is my sales datamy date tablethe relationshipthe outputthe relationship

Please help me, ive at it for 3 days now trying different ways

[Kieran_choudary]

Tried changin both to date/time still no luck

Here is the table\

Thanks for you help!

[Vvelarde]

@Kieran_choudary

Your order date column only have date or also have time?

[Kieran_choudary]

Now do you know any formulas to work out month on month growth %? haha

[Kieran_choudary]

it has time too

[Vvelarde · Accepted solution]

@Kieran_choudary

If you order date has time the relationship won't work because PBI can't find an exact column.

Not is the Same

Order Date  

01/01/2017 15:20  

Calendar Date

01/01/2017

[Kieran_choudary]

Spliting it worked thank you!!

[parry2k]

I always prefer to add datekey column in calendar and data table and set relation ship on that, that way, you never run into these kind of issues:

Thru power query add following column in both calendar and order table, and set relationship on this.

DateKey =DateTime.ToText([myDateField], "YYYYMMDD")

If you source field is Date Type then following use following:

DateKey = Date.ToText([myDateField], "YYYYMMDD")

Change DateKey to WholeNumber, not required thou.

Thanks,

P

[Kieran_choudary]

Shall i split the order date column? would that work?

[Vvelarde]

yes, you can split in a easy way in Query Editor:

Select the Column, Add Column-Date-Date Only

[Kieran_choudary]

I spoke too soon, now its putting all the revenue on each day

[Kieran_choudary]

No my fault, the relationship broke when i split the columns, now it is working 🙂

[alanhodgson]

@Kieran_choudary

Well, definitely something up with your relationship. Looks like your Calendar table might be off...

@Vvelarde Thoughts?

Best,

Alan

[Vvelarde]

@Kieran_choudary

First try:

Change Both DateTime Type to Date

[alanhodgson]

Hey @Kieran_choudary,

Your relationship looks good and I was able to recreate your calendar table. Let's test to make sure the relationship is working properly. Can you put the dates in a table and share the screenshot, like below? The first is from my "Source" table, the second date is from my "Calendar" table.

Cheers,

Alan