Grouping by Week Starting Date as Monday and filtering by Year, Month, Week and Date

[vopatel]

I have an Oracle database table which has a column with datatype date. I would like a clustered column chart where I can drilldown by year, quarter, month, week and day. When I drop a date column of my table in X axes, I can only see default filters for year, quarter, month and day, so I created new group on the date column with size of bin as 7 and added on shared axis as shown in screenshot. This way I am able to drill down by week fine, but the problem is week starts on Sunday, while I need week to start on Monday.  See the screenshot https://pasteboard.co/GS4reUt.png

Not sure how I can do that.

[Anonymous]

1. Have you made sure week AND day in your DimDate Query passes the statement number for starting on Monday i.e:
"WeekNum"; WEEKNUM([Date];2);
"WeekDay"; WEEKDAY([Date];2);

2. Next check your Table in Power BI and see if the Day name "Monday" is Sorted by the numeric value of WeekDay "1".



3. Lastly The Hierchy should look like this for drilldown to work properly (I'm guessing Saturday and Sunday may present a problem due to NULL values if you want to include them):



Hope you find a solution here.
Please upvote or mark as solution so I can get my 1st Badge If I helped you!

Cheers!

[v-chuncz-msft]

@vopatel,

You may create a calculated column instead.

Week =
Query1[ORDER_DATE] - WEEKDAY ( Query1[ORDER_DATE], 3 )

[vopatel]

Didnt work as expected. When I drill down to week, I see numbers jumping by 5 on X axes. See https://pasteboard.co/GSofr0Y.png

I would like to see week start date(Monday) on X axes.