It is not an ideal solution, but the formula does not allow to make calculations between interval and day.
I hope this may help someone:
Create a new calculated field:a. Go to the "Data" tab and click on the "Add a field" button.b. Give your new field a name, such as "Week Starting Sunday".c. In the formula section, use the following formula:
CASE
WHEN WEEKDAY(Date) = 6 THENĀ DATETIME_SUB(Date, interval 6 day)
WHEN WEEKDAY(Date) = 5 THENĀ DATETIME_SUB(Date, interval 5 day)
WHEN WEEKDAY(Date) = 4 THENĀ DATETIME_SUB(Date, interval 4 day)
WHEN WEEKDAY(Date) = 3 THENĀ DATETIME_SUB(Date, interval 3 day)
WHEN WEEKDAY(Date) = 2 THENĀ DATETIME_SUB(Date, interval 2 day)
WHEN WEEKDAY(Date) = 1 THEN DATETIME_SUB(Date, interval 1 day)
WHEN WEEKDAY(Date) = 0 THEN DATETIME_ADD(Date, interval 0 day)
END
- Use the new calculated field as a dimension:a. Go to the "Data" tab, and select the calculated field you created.b. Drag and drop it into the "Dimensions" section of your report.c. Use the new dimension to group your data by week, starting on Sunday.P.S. If you need to get the end of the week also use another formula:
CASE
WHEN WEEKDAY(Date) = 6 THEN DATETIME_ADD(Date, interval 1 day)
WHEN WEEKDAY(Date) = 5 THEN DATETIME_ADD(Date, interval 2 day)
WHEN WEEKDAY(Date) = 4 THEN DATETIME_ADD(Date, interval 3 day)
WHEN WEEKDAY(Date) = 3 THEN DATETIME_ADD(Date, interval 4 day)
WHEN WEEKDAY(Date) = 2 THEN DATETIME_ADD(Date, interval 5 day)
WHEN WEEKDAY(Date) = 1 THEN DATETIME_ADD(Date, interval 6 day)
WHEN WEEKDAY(Date) = 0 THEN DATETIME_ADD(Date, interval 7 day)
END