none
Weeknum should start at every month

    Question

  • Hi All,

    we have a requirement of calendar in power pivot chart,charts interactive with the calendar.

    in power pivot table 

    weeknum calculate the weeknumber for year(range 1-52)

    is their any possibility to calculate the weeknum for month(range 1-5)

    for every month it should start with 1

    Wednesday, May 08, 2013 6:45 AM

Answers

  • Assuming you have a proper Date dimension, you could do this as a calculated column in that table.  Basically, group all of the rows for each month together, and then subtract the weekNumberOfYear for the first week of the month from the current row's weekNumberOfYear.  Then add 1 so that week 1 is 1 and not 0. 

    Like this.

    =1+ [WeekNumberOfYear] - 
       (CALCULATE(MIN(DimDate[WeekNumberOfYear])
                               ,ALL(DimDate)
                                ,DimDate[CalendarYearMonth] = EARLIER(DimDate[CalendarYearMonth])
                             )
     )

    This assumes you have a column "CalendarYearMonth" in the format "yyyy-MM" or some other representation of both year and month together.  The Earlier groups all of the weeks for the month together.

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Wednesday, May 08, 2013 3:09 PM
    Answerer