In Level 17: Time Intelligence Functions: The DAX DATEADD() Function, you were introduced to the DAX Time Intelligence functions, beginning with the DATEADD() function. You learned that Time Intelligence represents a subset of the DAX formula language that supports the generation of time-specific analysis and the manipulation of data using time periods, including days, months, quarters, and years. These functions enable you to build and compare calculations over those periods.  In this level, you will meet the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions. These functions, like other level- and interval-specific Time Intelligence functions, are “hardcoded” to support ease of selection by authors and developers, and are named for ease of selection by analysts and report authors.

Much like other versatile DAX Time Intelligence functions, the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions are not limited to the comparison of simple aggregations like total sales or total expenses.  The functions can operate upon practically any calculation you might want to analyze, such as cumulative values, rolling averages, differences / variances between periods, percentage change, and many other metrics.

Moreover, Time Intelligence functions provide a convenient means for creating calculations that provide insight into other calculations, and it’s easy to see how this is true with the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions. The objective in this Level is to introduce each within the setting of relatively common business needs, but you will come across them again, particularly in various requirements of a periodicity / range / interval shifting nature, as you progress through the Stairway to DAX and Power BI.

Illustration 1: “End of Period” Functions at Work

You’ll create several measures, as you work through the various functions in this Level, that demonstrate the use of each function in a simple scenario, one each within a table visualization you’ll construct.  Combining the functions within a single table will allow comparison and contrast. These approaches mean that the Time Intelligence functions can be grouped into logical Stairway Levels and multiple functions can, in many cases, be explored in a single Level.

Additional Considerations for Time Intelligence Functions: Date Table Design Requirements

As a reminder that I like to include in any Level where we work with DAX Time Intelligence functions, it is important to remember that these functions rely upon a properly formed Date table to operate effectively.  If you are already aware of the considerations involved, you can skip to the next section.

Requirements of the Date table are as follows:

• The Date table must contain all days for each year involved.
  • Calendar Years: Each year must begin on January 1 and end on December 31.
  • Fiscal Years (only): Include all dates from the first to the last day of a fiscal year. Example: if the fiscal year 2022 starts on July 1, 2022, then the Date table must include all the days from July 1, 2022 to June 30, 2023.
• There needs to be a column (usually called Date) with a DateTime or Date data type, containing unique values.

• The Datecolumn is not required to define relationships with other tables, although it often does.
• The Datecolumn must contain unique values.
• If the Datecolumn contains a time part, the time part should not contain anything except a consistent placeholder – for example, the time would always be 12:00 AM.
• The table must be marked as a Date table in the model (via the Mark as Date Table setting), in case the relationship between the Date table and any other table is not based on the Date.
• The Datecolumn should be referenced within the enacted Mark as Date Table

NOTE:  For details regarding marking a table as the Date table, see the following: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

Hands-on practice with the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions in the next sections will make their respective objectives and operations clearer.  You’ll see, within the combined exploration of these functions, how each can be easily employed to aggregate, and otherwise manipulate, “end” values for the time periods indicated by their respective names, in meeting the requirements of clients or employers in the business environment. You’ll get an understanding of the purpose of each function, and gain hands-on insight into how it interacts with basic filters, via measures you’ll construct. Moreover, you will:

• Examine the syntax involved in exploiting each function;
• Undertake an illustrative example of the use of each function in a practice exercise;
• Review a discussion of the results you obtain in each of the steps of the practice exercise.

Preparation for the Practice Exercises in this Level

Assuming that you have installed Power BI Desktop (the illustrations in this Level reflect the September 2022 release), you are ready to download and open the sample Power BI Desktop file that you will use for hands-on practice with the concepts introduced in this Level.

NOTE:  The latest version of the Power BI application is available for free download at www.powerbi.com.

Download the Sample Power BI File (.pbix) for Use in this Level

The sample Power BI file you’ll be using contains fully imported data, and a few pre-existing calculations and visualizations that might be helpful in general. Calculations created in other Levels of the Time Intelligence subseries up to the time of your download will also appear (with a “z_” prefix). You will add the calculations and visualizations that form the focus in this step-by-step Level as you go.

To complete the exercises in a given Level, I suggest that, working on a blank tab, you complete the steps, and then compare your results with those pre-existing in the sample.  This approach will likely produce the best learning results.

Using the sample dataset provided will ensure that the results you obtain in following the detailed steps of the exercises agree to the results obtained, and depicted, in the Stairway Level, as you progress.

Once the sample .pbix file is downloaded, take the following steps to open it in Power BI Desktop.

1. Open Power BI Desktop.
2. Select File -à Open other reports from the splash dialog that appears upon entry, as shown.

Illustration 2:  Select Open Other Reports on the Splash Dialog that Appears

3. Navigate to the file you have downloaded.

Illustration 3:  Select the Downloaded Stairway to DAX File and Open …

4. Select the file and click “Open.”

The .pbix file opens and you arrive within the Report view, which consists of a several tabs (“EXERCISE EXAMPLES”) containing the solutions to the current Level, as well as solutions for past Time Intelligence subseries Levels, depending upon the date of your entering this Level.  As you are likely aware, you can tell you are in the Report view because the current view (of the three views available in the upper left corner, Report, Data, and Model) is indicated by the yellow bar to the left of the icon.

5. Click the Data and Model view icons, as desired, along the upper left frame of the Power BI Desktop canvas, as desired, to become familiar with the sample model.

“End of Period”: The DAX ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() Functions

The operation, and many potential uses, of the DAX ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions are very similar, except for the periodicity of each (which, of course, is conveniently embedded within their respective names).  I therefore combine the explanation of the three, somewhat, in this Level.

Discussion

The DAX “End Of” functions are identical in syntax – the “behind the scenes action” embedded in the operation of each, is indicated in the three letters following “ENDOF” in the name involved.

Syntax

Syntactically, the parameters you provide are specified within the parentheses to the right of each of the “ENDOF” in each of the functions discussed in this section as shown:

ENDOFMONTH()

ENDOFMONTH(<dates>)

ENDOFQUARTER()

ENDOFQUARTER(<dates>)

ENDOFYEAR()

ENDOFYEAR(<dates>)

The single parameter for each function is:

• Dates – A column containing date expression

Further Considerations

• These functions are not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
• The datesargument can be any of the following:
  • A reference to a date/time column,
  • A table expression that returns a single column of date/time values,
  • A Boolean expression that defines a single-column table of date/time values.
    • Constraints on Boolean expressions
      • The expression cannot reference a calculated field.
      • The expression cannot use the CALCULATE()
      • The expression cannot use any function that scans a table or returns a table, including aggregation functions.
      • However, a Boolean expression can use any function that looks up a single value, or that calculates a scalar value.

Return Value

According to the Data Analysis Expressions (DAX) Reference, the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions each return “a table containing a single column of date values.”

Practice

Next, you’ll put the “End Of” functions into action, with a compact sample data set in the provided Power BI model.  You will work via simple measures you create in Power BI, and be able to gain confidence that the practice measures are working correctly through viewing the results you obtain via a “self-checking” scenario. As the three functions with which we are concerned in this section are virtually identical in mechanical operation, you will combine your practice efforts with the three into one Table visual.

You’ll begin with a simple reporting need: the requirement to present Total Sales values by date in the Power BI model. In this case, you’ll need to generate Total Sales per day over the full fiscal year of 2020.

Once you have a Table visual in place for this purpose, you will add three measures to demonstrate the operation of each of the DAX ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions.  Adding these measures to the same Table will provide instant visual verification that the functions deliver the appropriate data (in this case dates) in the manner that you expect.

Practice Exercise: Illustrate the Operation of ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() through Individual Measures You Create

To prepare for some hands-on exposure to ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR(), you’ll install a simple, five-column Table, initially - to display Fiscal Year, Fiscal Quarter, Month, Date, and Total Sales amounts, filtered to approximate the timeframe of “the full 2021 Fiscal Year” (July 1, 2020 to June 30, 2021 and beyond). This table will resemble the one partially depicted below.

Illustration 4:  Your Goal with the Practice Table …

Install Table Visualization to Display Total Sales by Month

1. In the sample Power BI model, create a new tab (via the “+” button to the right of the right-most tab underneath the Power BI design area), and click-drag the tab to the Practice Canvases tabs section to the left of the Exercise Samples tabs, dropping it in the location there.
2. Right-click, and rename, the tab, as shown. (I called mine "End of Periods " Functions, but name yours as you prefer.)

Illustration 5:  Renaming the New “Practice Canvas” Tab …

3. Click the cursor on the new tab’s blank canvas.
4. Click the Table icon in the collection atop the Visualizations tab, to create a blank table on the canvas.

Illustration 6:  Create a New Table on the Canvas

5. Ensuring that the new table visualization is selected, add the following table.column pairs to the Columns section, underneath the Visualizations collection:

• Fiscal Year
• Fiscal Quarter
• Month
• Date
• Total Sales

Your additions should appear, within the Fields tab - Columns section, as shown.

Illustration 7:  Values Additions in the Fields Tab …

6. With the new table selected, click the Format (middle) button atop the Visualizations pane, as depicted.

Illustration 8:  Formatting the New Table

7. On the Visual tab of the Format pane that appears, set the following properties (and, again, you can do as you prefer):
   • Values > Values - Font size of 12
   • Column headers > Text – Font – Font size of 14, Header Alignment at Center

The Values and Column headers sections of the Visual tab appear, with modifications, as depicted:

Illustration 9:  Suggested Format Adjustments (for Readability)

Next, you’ll gain some exposure to the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions.  To do so, you’ll create measures in the practice section that follows.

Create Measures to Support the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() Functions

You’ll create a measure to demonstrate each of the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions in action, within the Table visualization you have created above. This will give you the benefit, once again, of creating “self-checking” measures within visualizations in Power BI – something I always like to do to afford myself “reasonableness” checking. The values delivered by the columns that contain your measures can be easily compared to the contents of the Table visualization that has already been put into place for this purpose, as you will see shortly.

NOTE 1:  As mentioned elsewhere, you will see solution measures (their names are preceded by a “z_”) in the _Measure table of the Fields pane. These measures are used within the EXERCISE EXAMPLES section to support visualization solutions in this and preceding levels of the Stairway to DAX and Power BI.  Ignore these for the time being, unless you want to use the DAX they contain, versus typing fresh the text in this Level, for copying and pasting the code into the measures that you create throughout.

NOTE 2:  You will also see, again, that there are two “title tabs” at the bottom of the downloaded Power BI model. The leftmost is labelled “PRACTICE CANVASES ---->”, and it is to the right of this tab you are invited to place the tab(s) you create in working through the practices exercises of this Level.  To the right of the “PRACTICE CANVASES ---->” section of the tabs in the model, you will see another tab labelled “EXERCISE EXAMPLES ---->”, which is a set of all examples we have created, to date, within the Time Intelligence subseries of the Stairway to DAX and Power BI.  The idea is to proceed in this fashion to make available a complete set of working DAX Time Intelligence examples within the last Level of this subseries.

ENDOFMONTH() Function

First, you’ll create a measure to generate the value of the last date of the current Month. Because you have already pulled Date data into the Table within its own column, you’ll be able to examine the Date for each row of the Table, too, and thus be able to visually compare row context with the date generated by the new measure.

Moreover, in accordance with my own approach to learning and working with functions, you’ll examine the output of each function in an environment where it will be easy to verify accuracy / completeness.  In the business environment the date outputs themselves might be redundant in most cases.  I’m showing the dates themselves here just for learning purposes.

To create the “End of the Month” measure, take the following steps.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, as depicted.

Illustration 10:  Click New Measure to Begin Design …

3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

ENDOFMONTH =
   ENDOFMONTH('Date'[Date])

4. Click the check mark icon on the left side of the Formula bar to commit the new measure, as shown.

Illustration 11:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the last date of the month that corresponds with the current row context (that is, “where you are” in the current month – which is dictated by the date row upon which the DAX is performed). That means that the end date of the month would be returned, say, for a row with the Date context of the tenth day of the month.  (You’ll get a clearer picture as the practice example unfolds.)

5. Ensuring that the new measure is selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “*3/14/2001 (Short Date),” leaving other settings at default.

Illustration 12:  Format the New Measure …

6. With the table on the canvas selected, add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields pane, as shown.

Illustration 13:  Add the New Measure to the Table Visualization

The new ENDOFMONTH measure is added to the Columns section of the Visualizations pane.

7. Click the new ENDOFMONTH measure in the Columns section, and drag it to the position above the Total Sales measure, as depicted.

Illustration 14:  Rearranging the New Measure to Place It to the Right of the Date Column …

The Table visualization appears, at this point, as shown.

Illustration 15:  Table Visualization at this Stage …

As you can see, the ENDOFMONTH measure appears to be working as expected.  For each date row, the last date of the month, for whatever the month is indicated to be in the Date field, is generated.  (If you scroll further down the Table, to the point of a month change you will note that the date in ENDOFMONTH shifts to the last of the next month with the end Date appearing for that month, as one might expect.)

The Total Sales depicted in each row corresponds to the sales of the indicated Date, perhaps obviously, and not the date of the ending of the month shown in ENDOFMONTH.

Next, you’ll see how to apply the date output of the DAX ENDOFMONTH() function to create a measure that generates the Total Sales corresponding to the date of the end of the month.

Generate the Total Sales at the “End of the Month” Date using ENDOFMONTH()

Next, you’ll create a measure to generate the value of the Total Sales as of the last date of the current Month. As I’ve stated earlier, the date outputs themselves would likely be redundant in most cases, but values as of those dates might be useful indeed.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, as you did with the measure you created earlier.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

EOM SALES = 
   CALCULATE ( 
      [Total Sales], ENDOFMONTH('Date'[Date]
   )
)

4. Click the check mark icon on the left side of the Formula bar to commit the new measure, again, as shown.

Illustration 16:  Commit the New Measure …

With this data set, you’re creating a measure to return “the Total Sales as of the end date of the month that corresponds to the current row context” (that is, “where you are” in the current month – which is dictated by the date row upon which the DAX is performed). That means that the last date of the month would be returned, say, for a row with the Date context of the tenth day of the month.

5. Ensuring that the new measure is still selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “Currency,” two (2) decimal places.

Illustration 17:  Format the New Measure …

6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new EOM SALES measure is added to the Columns section of the Visualizations pane. The Table visualization appears, at this point, as shown.

Illustration 18:  Table Visualization with New Measure Addition …

As you can see, in addition to the ENDOFMONTH measure, the new EOM SALES measure is operating as expected:  The “End of Month” sales value, like the “End of Month” date, shifts at the beginning of the new month (and continues to work in this fashion throughout the Table).

Next, you’ll gain some exposure to the ENDOFQUARTER() function, and create a measure to contain the function and to deliver its output to the Table we have set up already.  Again, because many steps are virtually identical to those we’ve taken with the ENDOFMONTH() function,  I’ll be showing illustrations only where differences occur between this and the other “to date” functions.

ENDOFQUARTER() Function

First, you’ll create a measure to generate the value of the last date of the current Quarter. Because you pulled Date data, in the preparatory section above, into the Table within its own column, you’ll be able to examine the Date for each row of the Table, too, and thus be able to visually compare row context with the date generated by the new measure.

To create the “End of the Quarter” measure, take the following steps.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, as you did in creating the earlier measure.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

ENDOFQUARTER =
   ENDOFQUARTER('Date'[Date])

4. Click the check mark icon on the left side of the Formula bar to commit the new measure.

With this calculation, you’re creating a measure to return “the last date of the quarter that corresponds to the current row context (again, “where you are” in the current quarter – which is dictated by the date row upon which the DAX is performed). That means, as an example, for any date within the third month of the second quarter, you would see returned, the date of the last, or end,  date in the second quarter. (Again you’ll get a clearer picture as the practice example unfolds.)

5. Ensuring that the new measure is selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “*3/14/2001 (Short Date),” leaving other settings at default, as we did with the date measure we created earlier.
6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new ENDOFQUARTER measure is added to the Columns section of the Visualizations pane.

7. Click the new ENDOFQUARTER measure in the Columns section, and drag it to be between the ENDOFMONTH and Total Sales measures, as depicted.

Illustration 19:  Rearranging the New Measure to Place It to the Right of the ENDOFMONTH Column …

The Table visualization appears, at this point, as shown.

Illustration 20:  Table Visualization with the New ENDOFQUARTER Measure in Place …

As you can see, the ENDOFQUARTER measure appears to be working as expected.  For each date row, the last date of the quarter, whatever the month is depicted to be in the Date field, is generated.  Moreover, the last date of the quarter shifts at the Fiscal Quarter change - as above, with the transition from the FY2018 Q1 to the FY2018 Q2 Quarter. If you scroll further down the Table, you will note that the date in ENDOFQUARTER shifts to last of the month with the last Date appearing for that quarter, as one might expect.

The Total Sales depicted in each row corresponds to the sales of the indicated Date, once again, and not the date of the end of the month shown in ENDOFQUARTER, as we noted earlier while working with the ENDOFMONTH() function.

Next, you’ll see how to apply the date output by the DAX ENDOFQUARTER() function to create a measure that generates the Total Sales corresponding to the date of the end of the quarter.

Generate the Total Sales at the “End of the Quarter” Date using ENDOFQUARTER()

While, as I’ve stated earlier, the date outputs themselves would likely be redundant in most cases, at least at the visualization level, values as of those dates are often more useful.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, as you did with the measures you created earlier.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears

EOQ SALES = 
   CALCULATE ( 
      [Total Sales], ENDOFQUARTER('Date'[Date]
   )
)

4. Click the check mark icon on the left side of the Formula bar to commit the new measure, again, as shown.

Illustration 21:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the last date of the quarter that corresponds to the current row context – which is dictated, again, by the date row upon which the DAX is performed. That means that the last date of the quarter would be returned, say, for a row with the date context of even the first day of the quarter.

5. Ensuring that the new measure is still selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “Currency,” two (2) decimal places.
6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new EOQ SALES measure is added to the Columns section of the Visualizations pane. The Table visualization appears, at this point, as shown.

Illustration 22:  Table Visualization with New Measure Addition …

As you can see, in addition to the ENDOFQUARTER measure, the new EOQ SALES measure is operating as expected:  The “End of Quarter” sales value, like the “End of Quarter” date, shifts at the beginning of the new quarter (and continues to work in this fashion throughout the Table).

Next, you’ll gain some exposure to the ENDOFYEAR() function, and create a measure to contain the function and to deliver its output to the Table we have set up already.  Again, because many steps are virtually identical to those we’ve taken with the ENDOFQUARTER() function, I’ll be showing illustrations only where differences occur between this and the other “to date” functions.

ENDOFYEAR() Function for a Calendar Year End

This time you’ll create a measure to generate the value of the last date of the current Year. And once again, you’ll be able to examine the Date for each row of the Table, and thus be able to visually compare row context with the date generated by the new measure.

To create the “End of the Year” measure, take the following steps.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, as you did in creating the earlier measure.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

ENDOFYEAR =
   ENDOFYEAR('Date'[Date])

4. Click the check mark icon on the left side of the Formula bar to commit the new measure.

With this calculation, you’re creating a measure to return “the last date of the year that corresponds the current row context (that is, “where you are” in the current year – which is dictated by the date row upon which the DAX is performed). That means, as an example, for any date within the second month of the year, you would see returned, the date of the last date in the Year.

5. Ensuring that the new measure is selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “*3/14/2001 (Short Date)” leaving other settings at default, as we did with the date measure we created earlier.
6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new ENDOFYEAR measure is added to the Columns section of the Visualizations pane.

7. Click the new ENDOFYEAR measure in the Columns section, and drag it to be between the ENDOFQUARTER and Total Sales measures, as depicted.

Illustration 23:  Rearranging the New Measure to Place It to the Right of the ENDOFQUARTER Column …

The Table visualization appears, at this point, as shown.

Illustration 24:  Table Visualization with the New ENDOFYEAR Measure in Place …

The ENDOFYEAR measure appears to be working as expected.

Next, you’ll see how to apply the date output from the DAX ENDOFYEAR() function to create a measure that generates the Total Sales corresponding to the date of the end of the year.

Generate the Total Sales at the “End of the Year” Date using ENDOFYEAR()

This time, you’ll create a measure to generate the value of the Total Sales as of the last date of the current Year.

1. Click the ellipses (“…”) to the right of the _Measure table within the Fields
2. Click New Measure atop the context menu that appears, once again.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

EOY SALES =
   CALCULATE (
      [Total Sales], ENDOFYEAR('Date'[Date]
      )
)

4. Click the check mark icon on the left side of the Formula bar to commit the new measure, again, as shown.

Illustration 25:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the last date of the year that corresponds to the current row context – which is dictated by the date row upon which the DAX is performed. That means that the last date of the year would be returned, say, for a row with the date context of the first day of the same year.

5. Ensuring that the new measure is still selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “Currency,” two (2) decimal places.
6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new EOY SALES measure is added to the Columns section of the Visualizations pane. The Table visualization appears, at this point, as shown.

Illustration 26:  Table Visualization with New Measure Addition …

As you can see, in addition to the ENDOFYEAR measure, the new EOY SALES measure is operating as expected:  The “End of Year” sales value, like the “End of Year” date, shifts at the beginning of the new year (and continues to work in this fashion throughout the Table).

ENDOFYEAR() for a Fiscal Year End

Adjusting the ENDOFYEAR measure you have just created only requires a few steps to allow for handling fiscal year ends.  You’ll walk through those steps now, using a copy of the existing measure, so as to retain the original for comparison purposes.

1. Click ENDOFYEAR within the _Measure table within the Fields pane, to expose the DAX, once again, in the Formula bar.
2. Using the mouse, highlight the DAX and click CTRL + C to copy to copy to the clipboard.
3. Right-click the _Measure table within the Fields pane, and select New Measure from the context menu that appears, as shown.

Illustration 27:  Creating a New Measure to House the Copied DAX …

4. Highlight the default text “Measure =” in the Formula bar that opens, and paste the DAX you just copied into the new measure formula space. Rename the measure ENDOFYEAR(Fiscal) to reflect a fiscal year within the DAX.

Your formula should look like this:

Illustration 28:  Creating a New Measure - Via Modification …

5. Click the check mark icon on the left side of the Formula bar to commit the new measure, again, as before.
6. Ensuring that the new measure is selected within the _Measure table, and within the Measure tools tab of the toolbar, format it as “*3/14/2001 (Short Date)” leaving other settings at default, as we did with the date measures we created earlier.
7. Add the new measure to the Table visualization via the checkbox in the _Measure table of the Fields pane, as you have done with all the measures you’ve created do far.
8. Click the new ENDOFYEAR (Fiscal) measure in the Columns section of the Visualizations pane, and drag it to be between the ENDOFYEAR and Total Sales measures, as depicted.

Illustration 29:  Rearranging the New Measure to Place It to the Right of the ENDOFYEAR Column …

The Table visualization appears, at this point, as shown.

Illustration 30:  Table Visualization with Last Measure Addition …

The ENDOFYEAR (Fiscal) measure appears to the right of the original ENDOFYEAR (the Calendar Year version), unsurprisingly displaying identical values to its predecessor ENDOFYEAR measure, as all remains the same for this pair of calculations, except the name, at this point.

9. Click the new measure in the Fields pane, to re-open the DAX in the formula space for editing

Within the DAX, you will add a comma after ENDOFYEAR('Date'[Date], to enable the <YearEndDate> parameter (similar to the one we encounter with the DATESYTD() function).  The pseudocode looks like this:

ENDOFYEAR(<dates> [,<year_end_date>])

The optional parameter [,<year_end_date>] supports a literal string containing a date that defines the year-end date, as a way of accommodating fiscal calendars. The default is December 31, if this optional string is left out.  All you are doing now is leveraging that <YearEndDate> parameter.

10. For the ENDOFYEAR (Fiscal) measure, within the ENDOFYEAR('Date' [Date]) section of the DAX, insert a comma (“,”) between [Date] and the closing parenthesis (“)”)

When you add the comma, the prompt for the [YearEndDate]option is triggered, as depicted.

Illustration 31:  Triggering the [YearEndDate] Option

Your objective is to have the cumulative annual totals to start in July and end in June the following year.  You therefore need to add “06-30” (year end of June 30) after the comma to obtain the appropriate support for your fiscal year ends.

11. Add “06-30” (including quotes) to the ENDOFYEAR() function, after the comma you have added.

The DAX expression for the new measure appears as shown:

ENDOFYEAR (Fiscal) = 
   ENDOFYEAR('Date'[Date],"06-30")

12. Click the check mark icon on the left side of the Formula bar to commit the new measure, as depicted.

Illustration 32:  DAX Expression for the New ENDOFYEAR (Fiscal Year) Measure

13. Scroll to the Table row dated (“Date”) 7/1/2018 (the beginning a new fiscal year).

The ENDOFYEAR (Fiscal Year) measure resets on the ending day of the new fiscal year, indicating that the ENDOFYEAR() function is performing as expected, now that the [YearEndDate]option has been set.

Illustration 33:  ENDOFYEAR (Fiscal) Reset Occurs at the Beginning of the New Fiscal Year …

An ENDOFYEAR (Fiscal Year) SALES measure (perhaps called EOY (Fiscal) SALES could be constructed exactly like the EOY SALES measure above, only with ENDOFYEAR (Fiscal Year) replacing the calendar equivalent, after the following manner:

EOY (Fiscal) SALES = 
   CALCULATE ( 
      [Total Sales], ENDOFYEAR('Date'[Date],"06-30")
   )

You now have a simple Table visualization that demonstrates the assembly and operation of the DAX “End of Periods” functions.  Moreover, this visual contains examples for how to manage both Calendar and Fiscal years with ENDOFYEAR().  And, as always, from this straightforward beginning, it’s easy to see how similar measures can be parameterized to afford run-time manipulation of these functions for extended flexibility.

Summary

This Level of the Stairway to DAX and Power BI is part of a DAX Time Intelligence subseries, within which I introduce multiple DAX functions – grouping functions that are similar in operation in some ways, so as to condense explanations. After reading a discussion of the general purpose and operation of each of the ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR() functions, you focused upon putting the respective function to work within the construction of a measure that demonstrated using it as a frame for reporting at representative summary levels that you might encounter in the business environment.

As part of this introduction, you examined the syntax involved with each function, and then constructed an illustrative example of the use of the function in a simple practice exercise.  Finally, for each example you undertook, you explored means of generating a self-validating results dataset to ensure accuracy and completeness of the results output by the measure you constructed to contain the respective function.