Answered by:
How working with dax measures weekly
Question

Hi,
I need to create measures depending on the week in the year.
Fe, how can I get the first/last date of the same week of a selected date?
They exist fe ENDOFYEAR, ENDOFMONTH functions but not ENDOFWEEK. Also DATESINPERIOD doesn't act on weeks.
Any ideas, please?
Thanks
Tuesday, January 29, 2013 5:49 PM
Answers

you can calculate FirstDayInWeek pretty easy:
FirstDayInWeek:=CALCULATE(FIRSTDATE(DimDate[FullDateAlternateKey]), VALUES(DimDate[WeekYear]), ALL(DimDate))
or the one below if you havent combined Week and Year
FirstDayInWeek2:=CALCULATE(FIRSTDATE(DimDate[FullDateAlternateKey]), VALUES(DimDate[CalendarYear]), VALUES(DimDate[WeekNumberOfYear]), ALL(DimDate))
same of course also works with LASTDATE() to get LastDayInWeek
to calculate for example the first day of the previous week you may use this calculation:
FirstDayPrevWeek:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],7,DAY)), VALUES(DimDate[WeekYear]), ALL(DimDate))
hth,
gerhard www.pmOne.com 
 Marked as answer by Ed Price  MSFTMicrosoft employee Saturday, September 14, 2013 12:19 AM
Tuesday, January 29, 2013 9:40 PMAnswerer 
to make the whole thing relative you can use this approach
create a new table with 1 column containting values from 10 to 10  i called the table and the column "SimpleCounter"
then i added a measure asRelativeWeeks:=IF(HASONEVALUE(SimpleCounter[SimpleCounter]);MAX(SimpleCounter[SimpleCounter]);0)
the final measure looks like this:
FirstDayRelativeWeek:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],7*[RelativeWeeks],DAY)), VALUES(DimDate[WeekYear]),ALL(DimDate))
this works just fine for me
 www.pmOne.com 
 Marked as answer by Ed Price  MSFTMicrosoft employee Saturday, September 14, 2013 12:19 AM
Wednesday, January 30, 2013 4:10 PMAnswerer
All replies

I've calculate these measures:
FirstDayInWeek:=calculate(MIN(Time[Date]); FILTER(Time; Time[Year] = VALUES(Dates_From[Year]) && Time[WeekInTheYear] = VALUES(Dates_From[WeekInTheYear]))) LastDayInWeek:=calculate(MAX(Time[Date]); FILTER(Time; Time[Year] = VALUES(Dates_From[Year]) && Time[WeekInTheYear] = VALUES(Dates_From[WeekInTheYear])))
It could be interesting a workaround for DATEADD.
Thanks
Tuesday, January 29, 2013 6:25 PM 
you can calculate FirstDayInWeek pretty easy:
FirstDayInWeek:=CALCULATE(FIRSTDATE(DimDate[FullDateAlternateKey]), VALUES(DimDate[WeekYear]), ALL(DimDate))
or the one below if you havent combined Week and Year
FirstDayInWeek2:=CALCULATE(FIRSTDATE(DimDate[FullDateAlternateKey]), VALUES(DimDate[CalendarYear]), VALUES(DimDate[WeekNumberOfYear]), ALL(DimDate))
same of course also works with LASTDATE() to get LastDayInWeek
to calculate for example the first day of the previous week you may use this calculation:
FirstDayPrevWeek:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],7,DAY)), VALUES(DimDate[WeekYear]), ALL(DimDate))
hth,
gerhard www.pmOne.com 
 Marked as answer by Ed Price  MSFTMicrosoft employee Saturday, September 14, 2013 12:19 AM
Tuesday, January 29, 2013 9:40 PMAnswerer 
Hi Gerhard, thank for your reply.
I think that for a weekly measure it is necessary to consider the year and so your second FirstDayInWeek is more proper.
In my case, I've a fact table, a time table and two disconnected time tables in order to manage from date and to date parameters.
Starting from FirstDayPrevWeek, it is possible to calculate measure respect to n weeks backwards:
FirstDayPrevNWeeks:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],(7)*n,DAY)), VALUES(DimDate[WeekYear]), ALL(DimDate))
where n represents the number of weeks.
In analogue manner it is possible to calculate LastDayPrevNWeeks. FirstDayPrevNWeeks and LastDayPrevNWeeks could be used for DATESBETWEEN function, in order to get the dates belonged to a certain week backwards.
Moreover, it is possible to calculate FirstDayNextNWeeks and LastDayNextNWeeks.
Tuesday, January 29, 2013 10:16 PM 
It could be interesting to see the behaviour of FirstDayInWeek and LastDayInWeek in a measure.
I've made some proofs using CALCULATE & DATESBETWEEN and CALCULATE & FILTER functions unsuccessfully.
Wednesday, January 30, 2013 7:49 AM 
to make the whole thing relative you can use this approach
create a new table with 1 column containting values from 10 to 10  i called the table and the column "SimpleCounter"
then i added a measure asRelativeWeeks:=IF(HASONEVALUE(SimpleCounter[SimpleCounter]);MAX(SimpleCounter[SimpleCounter]);0)
the final measure looks like this:
FirstDayRelativeWeek:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],7*[RelativeWeeks],DAY)), VALUES(DimDate[WeekYear]),ALL(DimDate))
this works just fine for me
 www.pmOne.com 
 Marked as answer by Ed Price  MSFTMicrosoft employee Saturday, September 14, 2013 12:19 AM
Wednesday, January 30, 2013 4:10 PMAnswerer