Forum Discussion

Chau's avatar
Chau
New Member
9 years ago
Solved

Week starting Wednesday

I need to do weekly reports for the week starting from every Wednesday to Tuesdays the week after. Can anyone help me how to change start of the week to Wednesday (instead of Sunday as default).    ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Create this as a calculated column wherever you want to do your date filtering (i.e. Date table)

    WEEKNUM = IF(
	WEEKDAY([Date], 1) < 4),
	WEEKNUM([Date], 1),
	WEEKNUM([Date], 1) + 1
)

     

    This will place Week Numbers next to all of your lines.  Now you can do your filtering based on Weeknumbers.  If you do this as part of a Date Dimension table, you can do a lookup from any date to the WeekNumber

Chau's avatar
Chau
New Member
9 years ago

Anonymous Thanks CheenuSing. Your method works as well. 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

I'd suggest, if you don't have one, create a Date Dimension table  (http://www.agilebi.com.au/power-bi-date-dimension/) which you can link to your other data.  The weeknumber formula you just created, could be placed into this table instead.  Now you'll have a direct link between Dates and Week numbers.  Lastly create another column which contains date data, but is something like "Week Starting" or "Week Ending".  This column will contain the data you will put on your axis.

A formula you can use for this is

[WeekEnding] = Calendar[Dates] – MOD(Calendar[Dates] - 5, 7) + 6