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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    YAY undocumented features lol.