Archive for June, 2013

DAX: Date and Time functions June 26th, 2013

Vinod Kumar

We have been looking at various DAX related content in this blog in the past few weeks, I thought let me start off by bringing out some of the commonly used DAX functions to light. Many are most useful and seem to work just like normal Excel functions. In this blog we will look at Date and time functions available with DAX.

The Date and Time functions convert string or number to a datetime value or convert a datetime value to a number representing the different part of the date time, such as year, month, day of a week, hour, minute.

The datetime argument can be an explicit datetime value, a column of datetime data type or an expression that evaluates to a datetime value. The result depends on the locale and date time setting which affects whether the datetime value is interpreted as mm-dd-yyyy, or as dd-mm-yyyy.

The value of the datetime argument can be in any of the proper datetime formats. Here are some example datetime formats:

  • "August 1, 2012"
  • "Sep 12, 2012"
  • "3-4-2012"
  • "2012-03-04"
  • "03/04/2012"

DATE Function

Syntax: DATE (yyyy, mm, dd)

Return type: DateTime

DATE function generates a date value from the separately provided value of year, month, and date. The year, month, and date arguments are rounded to integer if non- integer values are presented. The arguments can have explicit values, or expression evaluates to the value. The resulting date is generated using the following logic:

If yyyy is less than 1900,
     Resulting year = 1900 + yyyy
Else
     Resulting year = yyyy
If mm is between 1 to 12,
     Resulting month = mm
Else
     Resulting month = beginning of the resulting year + mm
If dd is between 1 and end of the resulting month
     Resulting date = dd
Else
     Resulting date = beginning of the date of the resulting year + dd

Examples:

=DATE(08,13,32)

The above example returns February 1, 1909.

=DATE(2010,1,-7)

The above example returns December 24, 2009

DATEVALUE Function

Syntax: DATEVALUE (date_text)

Return type: DateTime

DATEVALUE function converts date value represented in a text string to date value in DateTime type. The resulting date depending on the locale setting which controls how to interpret certain date format

Example:

=DATEVALUE ("8/1/2012")

The result in US will be August 1, 2012. The result in India will be January 8, 2012.

TIME Function

TIMEVALUE Function

Similar to DATE and DATEVALUE functions, TIME function converts the provided numbers for hour, minute, and second to a time value, TIMEVALUE converts a string time value to a time value of datetime type.

Syntax: TIME (hour, minute, second) and TIMEVALUE (time_text)

Return data type: datetime

DAY Function

MONTH Function

YEAR Function

WEEKDAY Function

WEEKNUM Function

Syntax: DAY(datetime)

MONTH (datetime)

YEAR (datetime)

WEEKDAY (datetime, <return_type>)

WEEKNUM (datetime, <return_type>)

Return data type: integer

These functions return the day number of the month (1-31), the month number of the year (1-12), the year, the day number within the week (0-6 or 1-7), or the week number within the year (1-53) from the datetime value passed to the argument. The return_type argument for the WEEKDAY function specifies when a week starts and whether the first day needs to return 0 or 1. If not specified, 1 is the default value.

  • 1 Week begins on Sunday (1) and ends on Saturday (7)
  • 2 Week begins on Monday (1) and ends on Sunday (7)
  • 3 Week begins on Monday (0) and ends on Sunday (6)

The return type for WEEKNUM function specifies whether a week starts on Sunday or Monday.

  • 1 Week begins on Sunday. Weekdays are numbered 1 through 7
  • 2 Week begins on Monday. Weekdays are numbered 1 through 7

EDATE Function

Syntax: EDATE (start_date, numberofMonths)

Return data type: datetime

EDATE returns the datetime before or after the number of months from the start_date. Useful for calculating the maturity date or due date that needs to be on the same day as the start_date. When the resulting day goes beyond the end of the month, the end of the month is returned.

Example:

=EDATE("3-4-2013", 3)

It returns June 4, 2013.

=EDATE("March 30, 2013", -1)

It returns February 28, 2013.

EOMONTH Function

Syntax: EOMONTH ( start_date, numberOfMonths)

Return data type: datetime

EOMONTH returns the datetime of the end of the month before or after the specified number of months from the start_date. Useful for calculating due date that needs to be the end of the month.

Example:

=EOMONTH("3/4/2013", 1)

It returns April 30, 2013.

HOUR Function

MINUTE Function

SECOND Function

Syntax: HOUR (datetime)

MINUTE (datetime)

SECOND (datetime)

Return data type: Integer

These functions return the number of hours (0-23), minute (0-59), and second (0-59) from the input datetime value.

NOW Function

Syntax: NOW()

Return data type: datetime

NOW returns the current date and time. Only updated when we refresh PowerPivot data. This is similar to Excel function.

TODAY Function

Syntax: TODAY ()

Return data type: datetime

TODAY returns the current date. Similar to NOW function, once the value is populated, it will not change until you refresh the PowerPivot workbook. This functions similar to Excel function.

YEARFRAC Function

Syntax: YEARFRAC(start_date, end_date, <basis>)

Return data type: decimal

YEARFRAC returns a fraction of the days between the start_date and the end_date to the total number of days in a year. <Basis> arguments defines the type of day count

Basis Description

  • 0 US (NASD) 30/360
  • 1 Actual/actual
  • 2 Actual/360
  • 3 Actual/365
  • 4 European 30/360

Hope you learnt the typical use of various functions with DAX. In future posts, we will take a look at other functions and how they work with DAX. Do let me know if you want me to take any other functions next.

Continue reading...


 

DAX Concepts Explained June 24th, 2013

Vinod Kumar

In this week, let me take an opportunity to write more about the DAX concepts. The previous posts include – Installing SQL Server 2012 Analysis Services–Tabular mode and Introduction to DAX in Excel.

To help understand how DAX functions work, particularly the ones that are used in the complex calculation scenarios, we need to spend some time on some of the key concepts that are the foundation for DAX functionality. This blog post talks about the terminologies used with DAX and how they function inside Excel or SQL Server Analysis Services.

Tabular database

Up to the current SQL Server 2012 release, DAX only works against tabular SSAS database. The tabular database can be a PowerPivot workbook inside Excel, a sandbox database loaded to SSAS instance running in SharePoint integration mode, or a tabular database deployed from a BISM model. In SQL Server 2008 R2 PowerPivot release, the entire tabular database needs to be stored in memory or error would be returned. In SQL Server 2012, paging capability is enabled so the data can be paged to disk if there is not enough memory for the entire database.

Table

Table stores data about a dimension (entity) or the fact data. Often a table gets data from an external data source, such as SQL Server relational database. For PowerPivot running in Excel, a table can also be a local table saved in the Excel file and linked to the PowerPivot workbook through the PowerPivot linked table feature or a table copied/pasted from a regular Excel workbook in a different Excel file.

Table Relationship

Tabular database supports many-to-one relationship between two tables. In tabular database, there can be only one relationship between any two tables. Typically two tables are related through the primary key and the foreign key constraint. From SQL Server 2012, we support multiple relationships between two tables, therefore some of the relationships would be based on non-key columns as well. When multiple relationships exist between two tables, only one relationship is active at any time and that relationship is used by default to navigate between the related tables. In order to use other relationship when evaluate a DAX expression, USERELATIONSHIP function is needed. For example, the following expression calculates the sales amount grouped by Ship Date.

=CALCULATE( FactInternetSales[TotalSalesAmount], USERELATIONSHIP( DimTime[TimeKey], FactInternetSales[ShipDateKey]))

Proper table relationship eliminates the need to maintain redundant data in multiple tables, and ensures DAX calculation returns the correct result. Table relationship also enables performing calculations involving looking up data in the related table by following the relationship. For instance, in the product table, if we create a calculated column with the following definition

=SUMX(RELATEDTABLE(‘FactInternetSales’),’FactInternetSales'[SalesAmount])

For each product in the product table, we will go to the internet sales table, get the subset of rows that contain sales data for the corresponding product, sum the value in the SalesAmount column in the matching rows and return the calculation result to the calculated column for the current product in the product table.

Column

Column is represented as ‘table name'[column name]. The data type for a column can be text, float, whole number, currency, date, and Boolean. These datatypes are same as what we discussed at Introduction to DAX in Excel.

Unique column

By definition, unique column does not allow duplicate value and null value. The uniqueness is enforced by setting the cardinality of the column to one. This property does not stop a null row from being added to the query results to represent the unknown member when necessary.

When table relationship is created, it internally sets the column on the one side of the one-to-many relationship to be unique, hence preventing duplicate value and blank value. When the relationship is deleted, the unique flag is removed from the column.

There is no way to mark a column as unique in PowerPivot for Excel add-in. In BISM, under Reporting Properties, one can set Row Identifier property for a column to be True. Setting a column as Row Identifier would mark the corresponding attribute as the key attribute of the dimension, which implies uniqueness as well. Only one column in a given table can be the Row Identifier (key attribute).

Row Context

Row context refers to the current row in the current table that is being evaluated. This applies primarily to the calculated column. For a complex calculation, row context may change as we switch from one DAX function to another DAX function. Row context controls the value that is returned for the column in the current function.

Here are some examples of calculated column definitions using AdventureWorksDW database as the data source to show the row context and row context change.

Calculated column in FactInternetSales table:

=’FactInternetSales'[SalesAmount] – ‘FactInternetSales'[TotalProductCost]

The above formula calculates the profit for each row in the FactInternetSales table. The row context is the current row being evaluated. As the calculation moves to the next row in the same table, the row context moves to the next row as well.

Calculated column in DimProduct table:

=SUMX( RELATEDTABLE( FactInternetSales), FactInternetSales[SalesAmount])

This calculates the total sales amount for each product in the product table. The calculation involves two row contexts, the first one is for the current row in the ‘DimProduct’ table. The second row context is the row in the ‘FactInternetSales’ table that is being scanned when we are inside the RELATEDTABLE function to retrieve the rows that have the same product key.

Filter Context

Filter context consists of members in row label, column label, vertical slicer, horizontal slicer and report filter in the PivotTable. It is used to evaluate the calculated measure. Filter context filters out rows in the selected workbook tables so the calculation is carried out on the selected rows after the filter.

When CALCULATE function is evaluated, if there is a row context associated, the row context is promoted to filter context, the SetFilter arguments passed to CALCULATE function will then be applied on top of the query context to produce the final context before we evaluate the expression passed to CALCULATE function.

I will try to explain these functions in future posts. This is just a start. In this post, let us just learn the concepts.

BLANK

BLANK in DAX is the same as ‘blank’ in Excel and ’empty cell value’ in MDX. PowerPivot table does not include blank row. For instance, COUNTROWS(Employee) includes the number of actual employees, not including one extra row for "null" or "unknown" employee.

A blank row will be added into the query result when we encounter referential integrity violations or null foreign keys. ALL() function will return all rows in the result including the blank row if added.

ALLNOBLANKROW will return the same result as ALL except the blank row will only appear if there is a blank row in the data – it will not appear if it is only present as the result of a referential-integrity violation or a null foreign key.

When we aggregate values (SUM, COUNT, AVERAGE, etc.) and all the values being aggregated are BLANK, the result will be BLANK. In arithmetic operations of addition and multiplication, we treat BLANK as zero when some operant is non blank value, for instance, BLANK+3+4+BLANK=7. When all the operant is BLANK, the result is BLANK, i.e. BLANK + BLANK = BLANK. In arithmetic division operation, when the nominator or the denominator is BLANK, the result is BLANK.

This behavior is different from Excel where BLANK is explicitly converted to zero when being calculated. The reason DAX do not convert BLANK value to zero is to avoid displaying members that do not have value. For instance, Cities table has 200 cities, for a particular week, only five cities have sales, when build PivotTable to show the sales for that week, and place cities on the row labels, we want to show five rows of data, instead of 200 rows with 195 of them being zero. We refer to this behavior as non-empty behavior, the same concept as in MDX.

NULL

Null has different semantic from BLANK. When arithmetic operation involves NULL operant, the result is always NULL. For example, NULL + 3 = NULL. NULL is not used in DAX. When we encounter a NULL value at import time, we convert it to BLANK.

These were some of the basic concepts we need to know when it comes to DAX. In next blog posts, we will look at the various functions available in DAX and learn more about this feature.

Continue reading...


 

Why do you settle for the Ordinary? June 21st, 2013

Vinod Kumar

It is second to human nature to sometimes accept the ordinary and move on with disgust. Why do we do that? Is there merit in what we do? Are we thinking balanced? Are we biased in our opinion? There are multiple schools of thought and I thought to bring some to discussion in this post. Do let me know if these arguments make sense and have you ever experienced any in your lifetime.

When you stop being nervous is when you should retire. I’m always a little nervous for anything I do because when complacency sets in, that’s when I feel it’s time to move on to something else – Chris Jericho

Work delusion

If you are working, there will always be times when each one of us go through this feeling. Now in this argument there is no correct or wrong approach. Let me walk through this.

Assume you work in a services company. After a successful submission of project to client, you are requested to join another team on a completely different technology than what you have been working. Your passion for the technology is so high that your heart starts to reject the very thought that you are switching the platform. There is nothing wrong in it and your mind is playing games if you reject then you will be in bench and if you accept you will be switching competencies.

Now think about the whole scenario from a different angle. If the organization is paying your monthly pay-cheques, then isn’t it our duty to accept what our organization throws at us (logically)? Isn’t it our obligation to work for organizations goals keeping our emotions out? If we have to work on our own terms, why are we working for an organization and not starting our own company to work in our own terms?

Do you see logic to where I am taking this argument? Don’t try to just disagree and commit as you accept the ordinary. Try to change something. Both arguments are correct in their own way.

The arrogance of success is to think that what we did yesterday is good enough for tomorrow ― William Pollard

In my opinion this is falling into the trap of accepting the ordinary or the Okay attitude.

What to do?

It is tough to push our limits from not accepting the ordinary. It took me quite some time to get to terms in understanding this behavior – be on the lookout for complacent attitude and that is your first signs of accepting the ordinary. This obviously is going to pose us some interesting challenges and we need to work our way out.

When we are no longer able to change a situation, we are challenged to change ourselves – Victor Frankl

Ideally to achieve this, you need to be in the right frame of mind, with the right energy in your system while you enjoy each and every moment as they come at you. When you see life is just going Okay, there are a number of options:

1. Do something about it and Change it or

2. Improve your perspective to start enjoying what you are doing.

Understand, sitting around and complaining doesn’t change the perspective or the situation either. So they are the only two option you have. No doing anything is not going to get us anywhere. Look at things around you and take a strong stance of changing it or changing its perspective.

Just because everything is different doesn’t mean anything has changed – Irene Peter

Don’t be Complacent with:

  1. Your thoughts
  2. Your passion
  3. Your choices
  4. Your motivations
  5. Your Friendship
  6. Your Job
  7. Your health
  8. How you treat people and How you get treated
  9. How you want to spend your time now
  10. Your actions
  11. How you feel every single day
  12. Your decisions
  13. Your holidays and time with family
  14. Your relationships
  15. What you want to give others

This list can be endless. Don’t get complacent because you found enjoyment in other parts of your life. A negative attitude can rub into other activities that you do and can become detrimental to the task in hand. As I wrap up, accepting the ordinary is not good enough – go ahead and change something.

The man who never alters his opinion is like standing water, and breeds reptiles of the mind – William Blake

Continue reading...


 

Excel Tip: Splitting Column Values June 19th, 2013

Vinod Kumar

This is in continuation to tips around Excel. This particular tip has been around in Excel for ages. But just like any other feature in Excel this is yet another nice utility that is lesser known to many.

Let us assume you have the following data set. This is a simple data set and it has Name that has come from some external source. Now we want to split as Lastname and FirstName instead of the usual Name column as shown below.

Now to split the column, create a new column “C” by inserting a new column adjacent to our name column. Next select all the values from Name column. With this selected, goto Toolbar “Data” Tab –> “Text to Columns” under Data Tools section.

Selecting this invokes a wizard which is easy steps. Select “Delimited” and press “Next”.

This is the screen where we will select the delimiter. In our Dataset, the Name field has a space. So we will select “Space” in this screen. And select “Next”.

Now we will be presented with the final screen. This can be used for some Formatting options. In my data, I am going to select the defaults. Press “Next”.

Our final screen shows the split automatically done. The screen would typically look like below. Give the Column “C” as Lastname and we must be done.

This option works wonders when data values are consistent and has just two parts or three parts for ALL data values. Else we will get additional columns and if there is data, a popup appears telling us there are values and do we need to override. Be careful in such cases.

Hope you have used these in the past and I will try to bring in more such tips in our subsequent blog posts.

Continue reading...


 

Excel Tip: Art of Summing with Excel June 14th, 2013

Vinod Kumar

Let me bring this weeks Excel Tips and Tricks series with a simple tip. The scenario I am talking is most used – You want to Build a Sum of values for a table of values.

The most common requirement for any table of data is to build a SUM or Average along its rows and Columns. There are multiple approaches and let me outline some of the most common ways it is done today and I find each of these methods interesting and one method will always be efficient than the other. I am sure I have not outlined all the possible ways of doing this task.

Initial DataSet

This is a simple dataset of names and various Sales number. Now we need to have create totals Month-wise as well as employee-wise. So our dataset looks like below:

And we want to build SUM across and along as shown below:

Using SUM Formula

The basic old school of thought will be to Type “=SUM” as shown in figure below and we can give the range of C3-C6. Similarly we will do it for each of the cells.

Copy-Paste: A more efficient way to get the values across will be copy the Cell C7 and paste it across in D7, E8 and so on. This copies the formula of sum and gets the relative cells accordingly for each cell.

Using Shortcut

When the Cursor is at C8, Press “Alt+” (Alt and + key). This generally gets the adjacent cells and does the auto select with the SUM option. This is really powerful and your screen would look like this:

Now that we have got the C8, we can do Copy-Paste. Wait, now if you move your mouse to the C8 cell corner – you will be presented with a cross-arrow. Just drag this till F Column. It will magically fill the formula and make the SUM.

Now let me move to the Columns G3 and to get the SUM, let me use “Alt+” and press Enter. Voila, you have the Sum across columns done. Now we can use the same cross-arrow and drag – hold on.

Once you see the cross-arrow in columns, just double click. You will automatically fill till G7. Awesome right?

Using Toolbar

I am sure you have used any of these previous techniques in the past. The most efficient way I have figured out for Summing is using the Toolbar –> Home –> Editing Section –> AutoSum.

For this to work, select the region and just one click from toolbar. The values are filled.

It is just not only for single click SUM, you can also get AVERAGE, COUNT, MAX, MIN and many more functions as shown in figure below.

The best part of this method is, we have not used any Keyboard keys and I call this “Magic with mouse :)”.

Finally, I complete this weeks Excel Tips series and do let me know your feedbacks, I will be more than happy to write more on these lines and your comments are most welcome if you want me to write more of these in the future. I can surely write based on your feedbacks and enthusiasm for this topic. Office is an ocean and we can never be lost for tips to share. Thanks again for reading this far.

Continue reading...