cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sayth Regular Visitor
Regular Visitor

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?

6 REPLIES 6
Super User
Super User

Re: Align Months to custom weeks

@Sayth 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.





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Sayth Regular Visitor
Regular Visitor

Re: Align Months to custom weeks

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. 

Sayth Regular Visitor
Regular Visitor

Re: Align Months to custom weeks

Has anyone got any ideas on this?

 

Still looking for answers.

Community Support Team
Community Support Team

Re: Align Months to custom weeks

Hi @Sayth ,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sayth Regular Visitor
Regular Visitor

Re: Align Months to custom weeks

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 Established Member
Established Member

Re: Align Months to custom weeks

@Sayth -

 

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.