Power Pivot Principles: Introducing the TOTALYTD Function

Welcome back to our Power Pivot blog.  Today, we introduce another time intelligence function.

TOTALYTD is a time intelligence function similar to the DATESYTD (you can read about the DATESYTD function here).  The TOTALYTD function, like all time series functions, requires strict ascending and contiguous dates (i.e. dates must include all dates in order between the first and last dates with neither any gaps nor any duplication) in order to work properly.

The TOTALYTD performs similarly to the DATESYTD function, but allows us to apply filters and specify a year end date.  The syntax of the function is:

Unlike the DATEYTD function we do not need to use this function with CALCULATE. Let’s create a measure to return the year to date (YTD) sales and filter on products assigned as category 4:

Now let’s look at our PivotTable report:

We can see that the Total Sales YTD 2 measure is returning with the YTD sales but only for category 4 products, ignoring any slicers.

Lastly, we can specify the year end date, let’s change the year end date to the end of May:

Note that the <year end date> has to be expressed as a string, therefore we use “31/5” to specify that the year end date is the last day of May.

Moving on to our PivotTable:

We can see that the “Total Sales YTD 2” measure now treats May as the end of the year.

That’s it for this week, hopefully we’ve taught you something new in Power Pivot. Stay tuned to our blog for more on Power Pivot.