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?

Advertisements
1 comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: