Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (47.2k points)

I have a date column at the date level. I would like to use the date level at the granularity of two weeks (biweekly). Could you please help me in this regard.

1 Answer

0 votes
by (17.6k points)
  • Calculation can be created to aggregate the dates at a biweekly level.

  • At this point of time in this case, I am using a date field called [Date]:

IF DATEDIFF('week', DATETRUNC('week', [Date]), #01/01/2015#, 'monday') % 2 = 0 THEN

    DATEADD('week', 2, DATETRUNC('week', [Date])) -1


    DATEADD('week', 1, DATETRUNC('week', [Date])) -1


  • This calculation performed above checks if a date is an even or odd number of weeks from the reference date (in this case #01/01/2015#). I have also defined weeks as starting on Mondays.

  • The difference between date1 and date2 expressed in units of date_part is returned by DATEDIFF(date_part, date1, date2, start_of_week). The start_of_week parameter is optional. The start of week is determined by the data source, if it is omitted.

  • Refer to this link for more details:

Related questions

Browse Categories