Forum Discussion

dexter's avatar
dexter
Icon for Helper II rankHelper II
8 years ago
Solved

creating a calculated column in the Date table

I have the below Date table, i want to add a new column which shows startOftheWeek date(in my case start of the week should be every thursday).
How can i calculate the start of the week(every thursday) and show that in the newly created column in the Date table below.

 

Date =
ADDCOLUMNS (
CALENDARAUTO ();
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)
Need Help
  • Chihiro's avatar
    Chihiro
    8 years ago

    Something like below. Apply formatting as required.

     

    Date =
ADDCOLUMNS (
CALENDARAUTO ();
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1
)

6 Replies

  • Chihiro's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Something like below. Apply formatting as required.

     

    Date =
ADDCOLUMNS (
CALENDARAUTO ();
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1
)
    • dexter's avatar
      dexter
      Icon for Helper II rankHelper II
      8 years ago

      What does this line do, can you please elloborate. Why are you substracting WEEKDAY([Date],14)+1?

       

      "startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1

       

      • Chihiro's avatar
        Chihiro
        Icon for Solution Sage rankSolution Sage
        8 years ago
        WEEKDAY formula with argument of 14 for pattern returns WEEKDAY number 1 for Thursday, ending with 7 for Wednesday.

        So date minus weekday number will give last Wednesday’s date. Add back 1 day and you get week start day of Thursday.