YTD Sales (Fiscal Calendar)

Maybe your requirement is slightly more complex, and you need to see the year-to-date sales based on your fiscal year end rather than the calendar year end date. The TOTALYTD function has an optional parameter that allows you to change the default year end date from "12/31" to a different date. Create a new calculated measure on your Internet Sales table using the following DAX formula:

Fiscal YTD Sales = TOTALYTD([Total Sales], 'Date (Order)'[Date], "03/31")

Now, let's take a look at both of these new measures in a table in Power BI:

Figure 17-Both the new measure in a table

The newly created measures YTD Sales and Fiscal YTD Sales have both been added to the preceding table. Let's take a closer look at how these two measures are different; the relevant sections in the table are annotated with the numbers one to four, corresponding to the following notes:

1. 1. The amount displayed for December 2005 is $3,266,374. This is the cumulative total of all sales from January 1, 2005 to December, 2005.
   2. As expected, the cumulative total starts over as the year switches from 2005 to 2006; therefore, the YTD Sales amount for January 2006 is $596,747.
   3. In the Fiscal YTD Sales column, the cumulative total works slightly differently. The displayed amount of $5,058,072 is the cumulative total of all sales from April 1st, 2005 to March 31, 2006.
   4. Unlike the YTD Sales measure, the Fiscal YTD Sales measure does not start over until April 1. The amount displayed for April 2006 of $663,692 is the cumulative total for April. This number will grow each month until May 31, at which point the number will reset again.