Could write a udf and take a value to tell it which day of the week should be 1 would look like this (drawing on answer from John to use MOD instead of CASE):
DROP FUNCTION IF EXISTS `reporting`.`udfDayOfWeek`;
DELIMITER |
CREATE FUNCTION `reporting`.`udfDayOfWeek` (
_date DATETIME,
_firstDay TINYINT
) RETURNS tinyint(4)
FUNCTION_BLOCK: BEGIN
DECLARE _dayOfWeek, _offset TINYINT;
SET _offset = 8 - _firstDay;
SET _dayOfWeek = (DAYOFWEEK(_date) + _offset) MOD 7;
IF _dayOfWeek = 0 THEN
SET _dayOfWeek = 7;
END IF;
RETURN _dayOfWeek;
END FUNCTION_BLOCK
To call this function to give you the current day of week value when your week starts on a Tuesday for instance, you'd call:
SELECT udfDayOfWeek(NOW(), 3);
Nice thing about having it as a udf is you could also call it on a result set field like this:
SELECT
udfDayOfWeek(p.SignupDate, 3) AS SignupDayOfWeek,
p.FirstName,
p.LastName
FROM Profile p;