Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Has anyone got any ideas on this?
Still looking for answers.
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
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.
@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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |