Compare Year to Date (YTD) Year-Over-Year

Example Scenario

You have following cost data for last 3 years:

• 2016: full year (1/1/2016 – 12/31/2016)
• 2017: full year (1/1/2017 – 12/31/2017)
• 2018: partial year and partial month of July (1/1/2018 – 7/11/2018)

How can you compare YTD changes year over year, i.e. Cost for period 1/1 – 7/11 in 2018 vs. 2017 vs. 2016?

1. Drag YEAR(Report Date) and MONTH(Report Date) to Columns
2. Drag SUM(Cost) to Rows
3. Create YTD Filter: we need to filter out dates from 7/12 – 12/31 in previous years (2017 and 2016)
   • Find Maximum Date available in the data
     Max Date = {max([Date])}
     // return 7/11/2018 no matter what date level is pulled in the view this is a fixed Level of Detail Calculation, check out more here
   • Filter out extra dates
     (DATEPART('month', [Date]) =  DATEPART('month', [Max Date])
     and
     DATEPART('day', [Date]) <=  DATEPART('day', [Max Date]))
     //for July, only keep those dates before or equal to 7/11
     OR
     DATEPART('month', [Date]) <  DATEPART('month', [Max Date])
     //for other months before July, keep all dates
4. Click on SUM(Cost) > hover over Quick Table Calculation > select Year Over Year Growth
5. Compare YoY without and with YTD Filter. You will get a more accurate picture when using YTD Filter.

Trick

Make sure YEAR(Report Date) and MONTH(Report Date) are in discrete format (in blue color) instead of in continuous format (in green color). Otherwise the Year Over Year Growth option will be greyed out. See below:

Tip

You can use the same approach to compare

• Quarter to date year over year

• Week to date year over year

• or any other comparison as long as you have YEAR(Date) and another date level in the Columns.