Direct Query - Start of the Week Date

Question

I'm currently trying to create a page filter for all of my charts in Direct Query.

The filter I need is the start of the week date . As I'm using Direct Query I can't manually enter start of the week dates e.g.

01/04/2019	1
08/04/2019	2
15/04/2019	3

So I've created a datekey via this code:

DateKey = CALENDAR(DATE(2000,01,01),DATE(2050,12,31))

WeekStartDate = DateKey[Date]- WEEKDAY(DateKey[Date],2)+1 // Monday as Week start date

However this creates a many to many relationship error with my core data table:

So I have created an additional table (piggybacking off of the datekey to SUMMARIZE the "WeekStartDate" :

SoW Index = SUMMARIZE(DateKey, DateKey[WeekStartDate])

Index = RANKX(ALL('SoW Index'),'SoW Index'[WeekStartDate],,ASC)

And created a relationship to my core table with this new (piggyback) table:

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
Based on your description, I created data in SQL SERVER to reproudce your scenario.
SOW:

&nbsp;
Then I connected to the data source with Direct Query mode. I created a calculated table and a calculated column as below.
&nbsp;
Calculated table:
DateKey = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

Calculated column:
WeekStartDate = DateKey[Date]-WEEKDAY(DateKey[Date],2)+1
&nbsp;
&nbsp;
There is a relationship between two tables based on 'Date' column.
&nbsp;
Result:

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;