Building relationship between data model


I will like to seek you advise on how to build the relationship on data model. I have a customer count table as follow:-

Store code Store name Weekname Customer Count WeekofYear Year
101 ABC 4 Jan 2017 1000 1 2017

Customers count in the table above are by store, by week and weekly count from year 2017 to Mar 21, 2018.

I have another date table which I used the date table quote as published in Enterprise DNA.

I have issue on building the relationship between these 2 table as there is no unique identifier in the data table. Please advise how I should link up these 2 tables.

Please also advise on the measures for YTD & LY YTD customer count.



When using a date table, you should always look to build a connection between the date column (in the date table) and a column in your fact table that has a date in it.

So in your customer count table you need to find the column that represents the date.

It would be a one to many relationship here also.

Certainly place a picture of the model once you give this a go and if it’s still not working.

Also certainly review this course for all the best practice tips on building effective data model. It’s a really crucial part of Power BI development


Once you have your model setup, review this video for YTD and LYTD.

I’ve also included other content that you can use also. Lots to learn!