Power Pivot Principles: Introducing the DATESYTD Function

Welcome back to our Power Pivot blog.  Today, we introduce one our first time intelligence functions.

DATESYTD is a time intelligence function that, like all time series functions, requires contiguous dates in order to work properly (although some are becoming “forgiving”).  The DATESYTD function returns values in a PivotTable that contain aggregated data for the year to date.  The syntax of this function is:

It’s commonly used with our old friend, the CALCULATE function. To demonstrate, let’s create a measure using the DATESYTD function to show year to date sales:

The next step is to create our PivotTable report.

We can now see the total sales for the month and the total sales YTD (year to date) throughout the year.  Notice that the year and month are both in the same column (column B) of the PivotTable.  For many functions, this is necessary for the time series calculation to work correctly.  If years were put in row 3 instead with months down column B, many formulae would not work as intended – hence the need for what is known as contiguity.

That’s it for this week!

Stay tuned 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.