Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Align Months to custom weeks

So I created custom weeks to start on a Saturday using a formula

 

= 'Calendar'[Date]  - WEEKDAY([DATE]+1,1)+1

Now however months don't align to the custom weeks. For example, in January the 31st was a Thursday. The custom week began on the 26th January and correctly ends on the Friday 1st February and the 1st week of February starts Saturday the 2nd.

 

However, the month of January ends on Thursday and the month of February starts on Friday. I need the months to adhere to the weeks. How have you solved this problem?

 

I have worked a formula that determines whether I need to CopyDown the date from the month Column or CopyUp. Essentially any days = Tuesday, Wednesday or Thursday where they contain the EndofMonth they should copy the current month down to the Friday.

 

=IF(ENDOFMONTH('Calendar'[Date])='Calendar'[Date],IF('Calendar'[Day Of Week] = "Tuesday" || 'Calendar'[Day Of Week] ="Wednesday" || 'Calendar'[Day Of Week] ="Thursday","CopyDown","CopyUp"),'Calendar'[Month])

but how do I copy down or Up in the formula?

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous it is all about calendar dimension in your model, you can control how your week/months to be used. here is a link which can be helpful and you can make the changes accordingly to your calendar table in your model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Apologies but I cannot see information on that page. There is a link to another page that alludes to 4-4-5 calendars but those are not demonstrated. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Has anyone got any ideas on this?

         

        Still looking for answers.