Forum Discussion
Custom Weeks for Seasonal Events
Dear Community Members,
Requesting your help on creating custom weeks for seasonal events that happens in different months in different years.
The data has "Registration Dates' and 'Years- Season'. Every year may have different start and end dates for the event but if I could have a column that starts counting the number of weeks for that season from the 1st date of registration as week 1 and reset and restart counting from week 1 for every change in the 'Year' Column.
e.g.
| Registration Date | Year - Season | Desired Week Number |
| 31/Oct/2018 | 2018 | 1 |
| 01/Nov/2018 | 2018 | 1 |
| 01/Dec/2018 | 2018 | 5 |
| 01/Jan/2019 | 2018 | 9 |
| 01/Nov/2019 | 2019 | 1 |
| 15/Nov/2019 | 2019 | 3 |
| 15/Jan/2020 | 2019 | 6 |
and so on.
best regards,
Hi rtiwari,
If you only consider with specific days instead of fact week numbers, I'd like to suggest you use DATEDIFF function to calculate get diff days from start date, end date and divide with period number(7) to get custom week number.
After these steps, you can use a number with MOD function to process with above result week number to convert them into the ranges.
Notice: the start date, end date may across many years will get a number over year week numbers limits, mod function will help convert them into the available range.
Regards,
Xiaoxin Sheng
3 Replies
Hi rtiwari,
So you mean the register date as the fiscal start date to calculate week numbers? If that is the case, you can normally get the current week numbers and setting an offset to calculate the fiscal week numbers.
Fiscal WeekNum = VAR registDate = MINX ( 'Table', 'Table'[Registration Date] ) VAR WeekNumofRegistDate = WEEKNUM ( DATE ( YEAR ( 'Table'[Registration Date] ), MONTH ( registDate ), DAY ( registDate ) ), 2 ) VAR endWeekNum = WEEKNUM ( DATE ( YEAR ( 'Table'[Registration Date] ), 12, 31 ), 2 ) VAR _offset = endWeekNum - WeekNumofRegistDate VAR currWeekNum = WEEKNUM ( 'Table'[Registration Date], 2 ) RETURN IF ( 'Table'[Registration Date] < DATE ( YEAR ( 'Table'[Registration Date] ), MONTH ( registDate ), DAY ( registDate ) ), IF ( YEAR ( 'Table'[Registration Date] ) = YEAR ( registDate ), //same year currWeekNum + _offset, //different year currWeekNum + WEEKNUM ( DATE ( YEAR ( 'Table'[Registration Date] ) - 1, 12, 31 ), 2 ) - WeekNumofRegistDate ), currWeekNum - WeekNumofRegistDate + 1 )In fact, these calculations still have some trouble to calculate when the expression tried to calculate between different years. (notice: weeknum function will be reset when date across the year end)
Regards,
Xiaoxin Sheng
Thank you Xiaoxin for your help. As you already mentioned that the WEEKNUM function may not be the right formula for this situation as it resets at calendar year end. I would be happy to call the desired week column rather as a 'Phases' of a campaign, if that helps. The 'Week' idea was just to have an equivalent period for the season across the various year of the event. I could even add this information in the source data itself if there was an Excel formula that could do the same i.e. allocate 7 days from the registration start date as a Phase -1 and allocate the 8th day from the registration start date as Phase 2 and so on. It will eventually reset the Phase count once the Year-Season column value changes to the next season.
I'm thinking maybe I could have a CALENDARAUTO table based on MIN and MAX registration dates and then add a column that clubs the 7 days as a Phase 1 and goes on. Or the longer way of having different Calendars for various years and eventually appending them all together.
Hi rtiwari,
If you only consider with specific days instead of fact week numbers, I'd like to suggest you use DATEDIFF function to calculate get diff days from start date, end date and divide with period number(7) to get custom week number.
After these steps, you can use a number with MOD function to process with above result week number to convert them into the ranges.
Notice: the start date, end date may across many years will get a number over year week numbers limits, mod function will help convert them into the available range.
Regards,
Xiaoxin Sheng
