locked
How working with dax measures weekly RRS feed

  • 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 -

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

    FirstDayRelativeWeek:=CALCULATE(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],7*[RelativeWeeks],DAY)), VALUES(DimDate[WeekYear]),ALL(DimDate))

    this works just fine for me


    - www.pmOne.com -

    Wednesday, January 30, 2013 4:10 PM
    Answerer

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 -

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

    RelativeWeeks:=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 -

    Wednesday, January 30, 2013 4:10 PM
    Answerer