im using spotfire software and i have a datetime column like:

how can i created a new calculated column where it instead changes the date to the monday of the week for all values?

many thanks

i can do this in power bi by dropping the time and using the function

Start of Week Monday = 'Data'[DateTime ] 1-WEEKDAY('Data'[DateTime ]-1)

this results in all the values changing to the monday of its given week/month/year

how can i do this in spotfire?

CodePudding user response：

Assuming you have the following functions available to you: DateAdd, DayOfWeek (*)

You could try:

DateAdd("dd",1 - (If(DayOfWeek([original_Date])=0,7,DayOfWeek([original_Date]))),[original_Date])

This resets the date (including the time portion) to the previous Monday.

Initially I had tried the simpler formula:

DateAdd("dd",1 - DayOfWeek([original_Date]),[original_Date])

but Sunday is mapped to a zero, so whenever the original day was a Sunday, it was pushed to the following Monday. It does depend on your original settings so you may have to play around with the numbers a bit.

(*) depending on what the source of your data is, equivalent functions may be available.