Data Model - Week number based on condition

[Michnia25]

Hi, I need help to set the week number based on the condition and i will be appreciated for some support.
Basically i need to add column with week number but the challenge which i facing is that the week number for my accounting start from 6am sunday morning till 5:59 am following sunday. Is there any way to set the condition based on the time and date column to set the week number?

 

[Matt Allington]

This is an interesting data modelling problem, and the approach depends in the detail. Do you need to do day level reporting, or only week level? If the later, the easiest approach I can think of is to add a unique WeekID (integer) to the transaction table and create a week level calendar table. Ie the first week ever would be 1, then 2….then 52 for the last week of year 1, then 53, 54…etc. join the 2 tables and go from there. What else you do depends on the detail, and id this approach works.

Also, best practice is to separate date and time into 2 columns. I recommend splitting your transactional date/time into 2 columns. You should also round the time to the highest grain you can live with. Eg, don’t load seconds if minutes will do, don’t load minutes if hours will do.

 

[Michnia25]

This is an interesting data modelling problem, and the approach depends in the detail. Do you need to do day level reporting, or only week level? If the later, the easiest approach I can think of is to add a unique WeekID (integer) to the transaction table and create a week level calendar table. Ie the first week ever would be 1, then 2….then 52 for the last week of year 1, then 53, 54…etc. join the 2 tables and go from there. What else you do depends on the detail, and id this approach works.

Also, best practice is to separate date and time into 2 columns. I recommend splitting your transactional date/time into 2 columns. You should also round the time to the highest grain you can live with. Eg, don’t load seconds if minutes will do, don’t load minutes if hours will do.

Thanks for that, to be honest with you I manage to achieve the final goal by splitting the date and time column then I created 2 condition columns which are checking if the date = to Sunday and if yes check if the time is <=06:00. Based on results if statement is = to True then week number = -1 or 0.
Not the most efficient way but is working

 

[Matt Allington]

This is a good solution - don't beat yourself up.

 

[bebo021999]

Simple to minus 6 hours, then use weeknum
= WEEKNUM(A1-6/24)
With A1 contains real date-time