# Sloppy DAX specifics

OK, here is the DAX calculation I’m not particularly proud of:

=IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (0 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (0 * 7) – 1), 0,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (1 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (1 * 7) – 1), 1,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (2 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (2 * 7) – 1), 2,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (3 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (3 * 7) – 1), 3,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (4 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (4 * 7) – 1), 4,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (5 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (5 * 7) – 1), 5,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (6 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (6 * 7) – 1), 6,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (7 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (7 * 7) – 1), 7,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (8 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (8 * 7) – 1), 8,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (9 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (9 * 7) – 1), 9,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (10 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (10 * 7) – 1), 10,

IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (11 * 7) – 7) &&

[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (11 * 7) – 1), 11,

BLANK()))))))))))))

What does it do?

It is a calculated column that calculates the number of weeks back a given row’s date field (the [DateValue] column) is from the current week. The catch is that for this calculation the week starts on Saturday. [DateValue] is a calculated column that strips out the time part of a datetime value and [NowValue] does the same for the NOW() function. I only want to calculate 12 weeks back. I also want to calculation to give the right answer every time the calculation is updated.

Anyone have a better way to do this?

Pingback: SWITCH to the rescue… | Reports from the field