Assistance with date table relationships

[jschoombie]

Hi everone,

I am having issues using a created date table.

When I created my date table I used the following formulas found on the below post:

http://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/td-p/8462

I have then created a relationship between the Date Table and the Sales Header table. The Sales Header table contains information regarding sales orders. 

The issue I face is that when I use the date from the Sales header table I can see a list of dates and corresponding order numbers.

If I select the order number but use the date from the date table you can see that only a handful of order numbers have corresponding dates.

Is someone able to shed some light on why this might be the case? 

Thanks!

[mnarmeen]

I have the same issue. can you please tell , how you resolved it ?

[ankitpatira]

@jschoombie From the screenshot it appears date in your sales table is defined as Text. Change column Data Type to date in power bi desktop via Data Modelling option and then joins should work.

[Anonymous]

@jschoombie

You do not have sales for every date.  So your date table has all dates available, and your sales table will have dates for all days with sales. 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

Thanks,

Ryan Durkin

[jschoombie]

Hi Ryan,

I realise that I don't have sales for every date, but if you look at the pictures there are lots of sales orders that should have dates next to them when I used the dates generated from the Date Table.

Do you have an idea as to why this might be the case?

[Anonymous]

Can you add measures such as Min(Sales[Date]),Max(Sales[Date]),Min(Dimdate[Date]),Max(Dimdate[Date])?  I would make sure your Date Dimension tables have enough dates to cover the dates in the Sales column. 

The other common mistakes I see are not marking the date table as a Date table and sometimes having the date field formatted as a text field. 

If you can attach some more screenshots, and maybe just use the actual date from the date table rather then the hiearachy I'll be better able to assist.

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

Thanks,

Ryan Durkin