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

Thanks

Tuesday, January 29, 2013 5:49 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:

hth,
gerhard

- www.pmOne.com -

Tuesday, January 29, 2013 9:40 PM
• 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 as

RelativeWeeks:=IF(HASONEVALUE(SimpleCounter[SimpleCounter]);MAX(SimpleCounter[SimpleCounter]);0)

the final measure looks like this:

this works just fine for me

- www.pmOne.com -

Wednesday, January 30, 2013 4:10 PM

### 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:

hth,
gerhard

- www.pmOne.com -

Tuesday, January 29, 2013 9:40 PM

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:

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 as

RelativeWeeks:=IF(HASONEVALUE(SimpleCounter[SimpleCounter]);MAX(SimpleCounter[SimpleCounter]);0)

the final measure looks like this: