SWITCH to the rescue…

In my post from yesterday, I showed a kind of messy DAX calculation from a workbook I’m building. I spent some time today trying to simplify the calculation. Here’s V2 of the calculation which is (I think) a lot less messy and hard to understand:

=IF([Date] <= [DateToday] – WEEKDAY([DateToday]) – 1,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – 7, 0,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (2*7), 1,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (3*7), 2,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (4*7), 3,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (5*7), 4,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (6*7), 5,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (7*7), 6,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (8*7), 7,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (9*7), 8,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (10*7), 9,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (11*7), 10,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (12*7), 11,

[Date] (formerly [DateValue]) is the column containing the date value of the row with the time part stripped off.

In the calculation itself, I first check if [Date] is later than the previous full Saturday – Friday week and if so, return BLANK() (the IF() function). If not, I use the SWITCH() function to peel off the weeks back one at a time until I get to the twelfth week. If the row is older than that, I return BLANK().

In researching the SWITCH() function, I found that, from an execution standpoint, the results are guaranteed to be evaluated in the order they appear in the SWITCH(). Because of that, I don’t have to check both ends of each weekly range. This de-clutters the calculation a lot.

Note, however, that while the result of the calculation is guaranteed from an order point of view, the actual execution of the SWITCH() cases is not guaranteed to be in order and short circuit evaluation is not guaranteed. So you can’t rely on cases not being executed if they come after the case that ends up being the one that fires and you can’t rely on the cases being executed in the order they appear.


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: