Direct Query - Start of the Week Date

[Anonymous]

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]

Hi, @Anonymous 

Based on your description, I created data in SQL SERVER to reproudce your scenario.

SOW:

Then I connected to the data source with Direct Query mode. I created a calculated table and a calculated column as below.

Calculated table:
DateKey = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

Calculated column:
WeekStartDate = DateKey[Date]-WEEKDAY(DateKey[Date],2)+1

There is a relationship between two tables based on 'Date' column.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Join SOW of Table with Date of Date Table. Start of Week in calendar/Date will take care of Display

[Anonymous]

However it's still not working. Can anyone advise on how to build start of the week date tables in Direct Query?

Thanks