cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chau Frequent Visitor
Frequent Visitor

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). 

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Week starting Wednesday

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


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

11 REPLIES 11
Ross73312 Super Contributor
Super Contributor

Re: Week starting Wednesday

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


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

CheenuSing Super Contributor
Super Contributor

Re: Week starting Wednesday

hi @Chau

 

Create a column in your calendar table

 

DayofWeek = Weekday(Calendar[Date],13)

 

The last parameter makes the week to begin from Wednesday to Tuesday.

 

If this works for you please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Ross73312 Super Contributor
Super Contributor

Re: Week starting Wednesday

@CheenuSing correct me if i'm wrong, but doesn't WEEKDAY only return a value between 1 and 7?  Also doesn't the 2nd parameter only accept values 1, 2, or 3 rather than the 13 you have suggested?

 

https://msdn.microsoft.com/en-us/library/ee634550.aspx

 

My understanding is that @Chau wanted a set of week numbers in order to do filters for reporting.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


CheenuSing Super Contributor
Super Contributor

Re: Week starting Wednesday

Hi @Ross73312

 

If you look up the WeekDay function in Excel the second parameter also accepts values from 11 to 17. 

 

The weekday function in dax wroks in the same manner as excel although the documentation you referred to does not say so.

 

You can try out various values 1 to 3, 11 to 17 as the second parameter and see what you get.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Ross73312 Super Contributor
Super Contributor

Re: Week starting Wednesday

YAY undocumented features lol.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Chau Frequent Visitor
Frequent Visitor

Re: Week starting Wednesday

@Ross73312 Thanks a lot Ross. It works perfectly for me. However I don't want the new WeekNum to appear on the X-axis. Instead I want to use the date of the start of the week on the axis. For example, instead of showing 14, 15, I want to show 29/03/2017, 05/04/2017 on the X-axis. How can I do that?

Chau Frequent Visitor
Frequent Visitor

Re: Week starting Wednesday

@CheenuSing Thanks CheenuSing. Your method works as well. 

Ross73312 Super Contributor
Super Contributor

Re: Week starting Wednesday

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

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


jcancu Frequent Visitor
Frequent Visitor

Re: Week starting Wednesday

Actually she is correct!

I have a client whose fiscal starts on 11/1/17 and their Week 1 = 11/1 - 11/7 so I used this formula after creating a new column to set the 11/1 to week 1, 11/8 to week 2, etc

Client Week = WEEKNUM([date],13)-44

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)