Forum Discussion
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
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
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.
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
YAY undocumented features lol.
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
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
Anonymous Thanks CheenuSing. Your method works as well.
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
Anonymous thank you that works a treat.
