Power Pivot Principles: The A to Z of DAX Functions – CURRENCY

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at CURRENCY.

The CURRENCY function

The CURRENCY function is used to evaluate the value of an expression and return it in the currency data type.  It employs the following syntax to operate:

• value: this is required.  Any DAX expression that returns a single scalar value.

It should be further noted that:

• the CURRENCY function returns the fourth [4^th] decimal digit by rounding up the fifth [5^th] significant decimal in value; rounding up happens if the fifth [5^th] significant decimal is equal to or greater than five [5].  For instance, the value of 9.012345 will return to 9.0123 after applying the CURRENCY function.  However, if the value is 9.888888, then converting to currency returns 9.8889.  As shown here:

• if the data type of the expression is Boolean (TRUE / FALSE), the CURRENCY function will return one [1] for TRUE and zero [0] for FALSE.  For example:

• the CURRENCY function will attempt to convert a string to a number if the expression's data type is Text; if the conversion is successful, the number will be converted to Currency; otherwise, an error is returned.  For example:

• when the expression's data type is DateTime, the CURRENCY function will convert the DateTime value into a number and then that number into Currency.  DateTime values consist of a fractional component that reflects the fraction of a day (where 12 hours or noon is 0.5 days) and an integer part that counts the whole days between the specified date and 1900-03-01.  An error will show up if the expression's value is not a valid DateTime value

• the CURRENCY function behaves similarly to using the CONVERT function with Currency as the second argument.  The two following claims are interchangeable:
  
  • Expression using the CURRENCY function:
    

• Expression using the CONVERT function:

Both of these DAX queries will produce the same result.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.