Align Months to custom weeks

[Anonymous]

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?

[parry2k]

@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]

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]

Has anyone got any ideas on this?

Still looking for answers.

[v-piga-msft]

Hi @Anonymous ,

I'm afraid that parry2k want to direct you to this article.

In additio, to understand your scenario better and get the solution quickly, could you share your data sample and your desired output so that we could help further on it?

Best Regards,

Cherry

[Anonymous]

My calendar data is just a standard calendar that you create in PowerPivot or PowerBI. I then added an extra column to reflect our weeks being Saturday to Friday.

This is the formula I used.

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

I then wanted to add an extra column to adjust the month to end on the Friday. So a work months calendar.

[ChrisMendoza]

@Anonymous -

So in your example, you mean to show Feb. 1 as a date in the 'First period' of the year which you call January and Feb. 2 as a date in the 'Second period' of the year which you call February?

So you need to make custom periods like:

Period 1 = Jan. 5, 2019 through Feb. 1, 2019

Period 2 = Feb. 2, 2019 through Mar. 1, 2019

etc. etc.??

The only way i was able to accomplish this was using Power Query, defining the [PeriodStartDate] and [PeriodEndDate] -> assign an Index -> generate list between -> expand -> etc. Basically, I could not find a way to use the 'normal' date functions but rather I had to redefine/make as 'custom periods'. In my opinion this was easier than trying to figure out or write some intricate IF like you propose.