DAX functions in Excel that everyone should have

, , ,

Excel 365 for Windows is getting more functions only for Data Models with PowerPivot.  Data Analysis Expression (DAX) functions are useful for exploring large datasets. There are a few UTC time/date functions we’d like to see made available to all Excel users.

A full list of the new DAX functions are below, two of those features caught our eye because they are functions users have wanted in Excel for many years.

The new DAX functions might be familiar to PowerBI users who already have these and more functions.

Getting UTC time in Excel

Getting the standard UTC time and date in Excel is important but not possible without some VBA. See Easy way to get UTC / GMT current time in Excel .  Excel has NOW() and other functions which return the current date/time according to the current computer setting but no direct way to get the time independent of the computer location or setting.

It’s frustrating to see that the new DAX functions include two functions that return the UTC/GMT date or time, something that all Excel users deserve to have.

UTCNOW
UTCTODAY

See also Get the local time zone offset into Excel a related omission from Excel’s in-built functions.

How about Microsoft make some UTC and UTC offset functions available to all Excel customers? 

How to use DAX functions

Start with an Excel Data Model then go to Power Pivot | Measures | New Measure.  Make a new measure with a formula that can include DAX functions.

Source: Microsoft

Once that’s done, make a PivotTable from the same Data Model. In the PivotTable fields list will be the Measure/DAX function that you can drag into the PivotTable.

Who gets it?

To get the new DAX functions you need:

  • Excel 365 for Windows
  • With PowerPivot
  • Originally only for Beta Channel users v 2208 build 15504.10000 or later.

In practice, the easiest way is to try using one of the new DAX functions to see if it’s available.

DAX function full list of new functions

Microsoft has a DAX function referencen, here’s a full list of the new functions.

  • ACCRINT
  • ACCRINTM
  • ALLCROSSFILTERED
  • AMORLINC
  • BITAND
  • BITLSHIFT
  • BITOR
  • BITRSHIFT
  • BITXOR
  • COALESCE
  • COLUMNSTATISTICS
  • COMBINEVALUES
  • CONTAINSSTRING
  • CONTAINSSTRINGEXACT
  • CONVERT
  • COUPDAYBS
  • COUPDAYS
  • COUPDAYSNC
  • COUPNCD
  • COUPNUM
  • COUPPCD
  • CUMIPMT
  • CUMPRINC
  • DB
  • DDB
  • DISC
  • DISTINCTCOUNTNOBLANK
  • DOLLARDE
  • DOLLARFR
  • DURATION
  • EFFECT
  • ERROR
  • FIRSTNONBLANKVALUE
  • FV
  • GENERATESERIES
  • IF.EAGER
  • INTRATE
  • IPMT
  • ISAFTER
  • ISINSCOPE
  • ISPMT
  • LASTNONBLANKVALUE
  • MDURATION
  • NAMEOF
  • NOMINAL
  • NONVISUAL
  • NORM.DIST
  • NORM.INV
  • NORM.S.DIST
  • NORM.S.INV
  • NPER
  • ODDFPRICE
  • ODDFYIELD
  • ODDLPRICE
  • ODDLYIELD
  • PDURATION
  • PMT
  • PPMT
  • PRICE
  • PRICEDISC
  • PRICEMAT
  • PV
  • QUARTER
  • RATE
  • RECEIVED
  • REMOVEFILTERS
  • RRI
  • SELECTEDVALUE
  • SLN
  • SYD
  • T.DIST
  • T.DIST.2T
  • T.DIST.RT
  • T.INV
  • T.INV.2T
  • TBILLEQ
  • TBILLPRICE
  • TBILLYIELD
  • TREATAS
  • UNICHAR
  • USEROBJECTID
  • UTCNOW
  • UTCTODAY
  • VDB
  • YIELD
  • YIELDDISC
  • YIELDMAT

A different Dax

Admission: I would not know a DAX function if I tripped over it.  The only ‘Dax’ I know are Jadzia and Ezri Dax from Star Trek: Deep Space Nine <g>.

Timelines for date filtering Excel PivotTables
Multiple Selections in Slicers for Excel PivotTables
Make an Excel PivotTable with multiple or nested rows