locked
DAX: last week date using weekNo. RRS feed

  • Question

  • Hi all, 

    I´m close to a solution.

    I have the weekno. and I´d like to retrieve the last week date of that weekno.

    Lets say I´m on weekNo. 51 of dec 2012, so last weekdate would be 23/12/12

    I have tried the following:

    =DATEADD(Dates[PK_Date];(-1*[WeekNoCalc])+7;DAY)

    that makes current week day no. *-1+7

    example:

    today is wednesday (3): -3+7=4. so we move 4 days (until sunday)

    the problem with this calculation is that if we are, for example in 31th of dec 2012 it says the week goes until 06/01/13.

    http://www.calendario-365.es/calendario-2012.html

    any ideas?

    thanks!


    • Edited by the_txeriff Wednesday, February 20, 2013 10:03 AM
    Wednesday, February 20, 2013 9:43 AM

Answers

All replies

  • if you want the calculation in a calculated column you may try this calculation:

    =CALCULATE(MAX('Date'[Date]), FILTER(ALL('Date'), 'Date'[Calendar Year] = EARLIER('Date'[Calendar Year]) && 'Date'[Week Of Year] = EARLIER('Date'[Week Of Year])))

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, February 21, 2013 9:09 AM
    Answerer
  • I think I was in a mistake.

    31th of dec 2012 is part of week1 of 2013.

    So te problem is not that. actually Im trying to rollback 4 weeks from this week

    So, if today is thursday I would go to sunday and from there rollback 4 weeks. The problem is that the datesinperiod, dateadd.. don´t have weeks, they can be used with days, months ad years.So if I rollback 28 days its not 4 weeks, and displayed data using weekno in dim.date shows incorrect.

    So I might usedatesbetween() function If I can know the "-4 weeks" date.

    thanks a lot four your help Gerhard


    Thursday, February 21, 2013 10:30 AM
  • Marco Russo also recently blogged about how to cope with Weeks and Timeintelligence in DAX:
    http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

    hth,
    gerahrd


    - www.pmOne.com -

    Thursday, February 21, 2013 11:39 AM
    Answerer
  • thanks, I´ll take a read.

    Thursday, February 21, 2013 11:41 AM
  • Hi txeriff,

    What about this question? Please do let us know how things go.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, February 26, 2013 3:03 AM
  • Hi all

    Well, I read the marco russo article posted from Gehard. I asked to Marco in the blog but I know he´s busy and he cannot solve it all.

    He told me to create an incremental week (always increment dates) column but I dont see it very clear and I dont think its going to work with dim.Date.Weekno dimension as it will be "linking" properly.

    There are several comments and screen captures  by txeriff (me) here, at the bottom:

    http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

    If you can take a look it would be great!

    thanks!

    Tuesday, February 26, 2013 8:40 AM
  • this would give you consecutive week numbers of years:

    =RANKX(ALL('Date'), [Calendar Year] * 100 + [Week Of Year], [Calendar Year] * 100 + [Week Of Year], 1, DENSE)

    (also posted it at marcos article)


    - www.pmOne.com -

    Thursday, February 28, 2013 5:16 PM
    Answerer
  • Hi Gerhard,

    I was going to aswer in the sqlbi article, but I think its better to do it here.

    I just added your calculation. I noticed it starts from week 2, there is no week 1.

    I added to the calculation, changed it but now all rows come empty. no clue

    :=IF (
        HASONEVALUE( Date[Calendar_Year] )
            && HASONEVALUE(Date[WeekNoIncrem] );
        CALCULATE(
            SUM([Sales Amount Actual]);
            FILTER(    
                ALL( Date );
                Date[Calendar_Year] = VALUES( Date[Calendar_Year] )
                    && Date[WeekNoIncrem] >= VALUES( Date[WeekNoIncrem] )-3
                                                                    && Date[WeekNoIncrem] <= VALUES( Date[WeekNoIncrem] )
                    && Date[Pk_Date] <= MAX( Date[Pk_Date] )
            )
        )/4;
        BLANK()
    )

    thanks a lot

    Thursday, February 28, 2013 5:49 PM
  • I just adopet your calculation to my AdventureWorks model and it works just fine:

    Avg4Weeks:=IF( 
        HASONEVALUE('Date'[Calendar Year])  && HASONEVALUE('Date'[WeekNoIncrem]),
         CALCULATE( 
            SUM('Internet Sales'[Sales Amount]),
             FILTER(    
                 ALL( Date ),
                 'Date'[Calendar Year] = VALUES('Date'[Calendar Year])
                 && 'Date'[WeekNoIncrem] >= VALUES( 'Date'[WeekNoIncrem] )-3
                 && 'Date'[WeekNoIncrem] <= VALUES( 'Date'[WeekNoIncrem] )
                 && 'Date'[Date] <= MAX( 'Date'[Date] )
             )
         )/4,
         BLANK()
     )


    - www.pmOne.com -

    Friday, March 1, 2013 7:45 AM
    Answerer
  • Hi,

    I forgot to use weekNoIncrem as dim.date attribute. Now it shows data.

    the problem is that it doesnt seem to show correct results and of course, it shows a weekno isnt very useful for the user. Let´s see if we can show another solution for the client, easier.

    I appreciate all the help you all gave me in these posts.

    Friday, March 1, 2013 8:26 AM
  • so, what is wrong with the results?

    i think you were complaining about wrong results when the year changes

    in general i would remove the filter from [Calendar Year] and also from [Date] as those filters will make the results not compareable


    - www.pmOne.com -

    Friday, March 1, 2013 9:00 AM
    Answerer