If you’ve thought that, when you start using PowerPivot, will get rid of array lookup functions you were deadly wrong! Although the concept of a data model does not go hand in hand with the need to use these class of functions, it is sometimes necessary to search the arrays. To do this you will use the LOOKUPVALUE function, and how to use it, in combination with the WEEKDAY function, you will find out in the text that follows.
We have added and linked several tables in data model: Artikli (Items), Partneri (Partners) and Transakcije (Transactions). The transaction table contains a column with date of their occurrence. To allocate the day of the week for a given date it is necessary to create the calculated column:
Function WEEKDAY returns a number corresponding to given date. Its second parameter is used to emphasize day by which a week starts. If week starts in Monday it should have value 2.
In order to get names of the days we need to link them in some way with numbers returned by the WEEKDAY function. You should create another table in Excel containing two columns. In the example I’ll name it „Dani“ (Days). First column represents numbers, and the other names of the days. This table should be added to data model but there is no need to link it to existing tables.
In order to get the name of the day you need to write the formula using the LOOKUPVALUE function. It is used similarly to VLOOKUP, but it has a reverse syntax. First, you need to specify a column with values, then the column being searched, and finally the column by which search is performed:
If you’ve done it all right you need to get a column with the values of the day of the week that you can later use to make various reports.