上QQ阅读APP看书，第一时间看更新

String functions – Month, Year

Now that you have completed your first calculated column, let's build a calculated column that stores the month–year value. The goal is to return a month–year column with the two-digit month and four-digit year separated by a dash, making "MM-YYYY". Let's build this calculation incrementally.

Select the Date (order) table and then click New Column from the modeling ribbon. Write the following code in the formula bar and then hit Enter:

Month Year = 'Date (Order)'[Month Number of Year]

As you begin validating the code, you will notice that this only returns the single-digit month with no leading zero. Your next attempt may look something like the following:

Month Year = "0" & 'Date (Order)'[Month Number of Year]

This will work for single-digit months; however, double-digit months will now return three digits. Take a look at the following screenshot:

Figure 3-Displaying Month Year

To improve upon this and only return the two-digit month, you can use the RIGHTfunction. The RIGHT function returns a specified number of characters from the right side of a string. Modify your existing DAX formula to look like the following:

Month Year = RIGHT("0" & 'Date (Order)'[Month Number of Year], 2)

For a full list of text functions in DAX, please go to the following link:
https://tinyurl.com/pbiqs-text

The rest of this formula can be completed quite easily. First, to add a dash, the following DAX code can be used:

Month Year = RIGHT("0" & 'Date (Order)'[Month Number of Year], 2) & "-"

Complete the Month Year formula by combining the current string with the calendar year column:

RIGHT("0" & 'Date (Order)'[Month Number of Year], 2) & "-" & 'Date (Order)'[Year])

You may have noticed that the Year column has a data type of a whole number, and you may have expected that this numeric value would need to be converted to a string prior to the combine operation. However, remember that the ampersand operator will automatically convert both inputs into a string before performing the combine operation!