Add 'Week of' to Custom Hierarchy

Introduction

This Post Explains how to create a widget hierarchy that includes ‘week of’ as a member of the hierarchy. (Calculates first day of that week)

Business Case

As a business Analyst, I would like to drill down in a Bar Chart with Dated Sales Data from year to quarter, month, week and Day. In addition, in our organization week start on Tuesday.

Steps

Create Custom Field based on existing Date field to define ‘week of’ date. Depending on your approach to calculating week of, use one of the below formulas.

Day of week	Formula
Sunday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],0))),7) *-1)
Monday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-1))),7) *-1)
Tuesday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-2))),7) *-1)
Wednesday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-3))),7) *-1)
Thursday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-4))),7) *-1)
Friday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-5))),7) *-1)
Saturday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-6))),7) *-1)