How to get Monthday based on range ?

[Anonymous]

Our business is very focused on "weeks" in a given month.

So November would be reported as

Nov - Week 1 =  11/01/17 - 11/05/17

Nov Week 2 =  11/06/17 -  11/12/17

Now on the pivot tables.. I need to also include "Prior Year" therefore, If I can create MonthDays in the data load script.. I can reference them in the pivot tables thus the only set expression I would need to worry about is just the year

So the question is - how do I create a "MonthDay range" in a dimension

I attempted to do it below...

Concatenate(dimPeriodFilter)

Load

"Num" As [DateKey],

    'Nov Week 1 As [PeriodNameNov]

Resident TempCalendar

Where Monthday(date)) >= Date('11/01/2017')

And Monthday(date) <= Date('11/05/2017')

I want the output to be this,

Nov 1, Nov 2, Nov 3, Nov 4, Nov 5

Thanks !

;

[luismadriz]

Hi Steven,

Not 100% sure of what you're trying to achieve but if you want the week of the month based on a datefield in your calendar you could use this:

Month(DateKey)&' W'&Ceil((Day(DateKey)+WeekDay(MonthStart(DateKey),0))/7,1) as WeekoftheMonth

Please double check it as I haven't fully tested it, but it seemed alright. Let me know how did it go,

Cheers,

Luis

[luismadriz]

Hi,

Just wondering to know how you went about this one,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others