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 DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() 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 developers, analysts and report authors.

Operating only upon a date column, NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() return the next day, month, quarter and year, respectively, within an active filter context.  As you’ll see in this Level, a typical use or the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions is to find the corresponding occurrences of a value – even when further limiting the returned occurrence by other criteria, such as a specified location, product, or so forth.

As I’ve stated throughout this subseries, 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 NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions. The objective in this Level is to introduce each within the setting of relatively common business needs. You will likely encounter them in the business environment, as a part of various requirements where “following” day, month, quarter or year occurrences of a given transaction / activity are a focus of a given report or another query.

Illustration 1: “Next” Functions at Work

You’ll create measures, as you work through the various functions in this Level, that demonstrate the use of each function in a simple scenario, within a matrix visualization you’ll construct.  Combining the functions within the same matrix will allow comparison and contrast. This approach means that Time Intelligence functions can be grouped into logical Stairway Levels, and that multiple functions (four, within this case) can be explored within a single Level of the Stairway.

Additional Considerations for Time Intelligence Functions: Date Table Design Requirements

As has been the case with the other Time Intelligence functions within this Level of the Stairway to DAX and Power BI series, I will include a reminder that I share 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.

The 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 Date column is not required to define relationships with other tables, although it often does.
• The Date column must contain unique values.
• If the Date column 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 Date column 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

Meeting the Date table requirements ensures that time intelligence function output complies with the data lineage of the date column or table provided as an argument.

Hands-on practice with the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions in the next sections will make their respective objectives and operations clearer.  You’ll see, within this combined (while comparative) exploration of these functions, how each can be easily employed to return “next” data, within the active filter context, to enable you to meet 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 performs, 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 Stairway Level reflect the May 2023 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 Desktop application is available for free download at www.powerbi.com.

Download 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 host of pre-existing calculations and visualizations that might be helpful to you 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 as directed throughout, you complete the steps, and then compare your results with those pre-existing in the sample.  This approach will likely produce the best learning outcome.

Using the sample dataset provided will ensure that the results you obtain in following the detailed steps of the exercises agree to the results I obtained in writing this Level, and that I have 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 downloading the sample for 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, along the upper left frame of the Power BI Desktop canvas, to become familiar with the sample model.

“Next Periods”: The DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() Functions

The operations, and many potential uses, of the DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR()  functions are very similar, except that each function contains an embedded periodicity, as indicated by its respective title. I combine the explanation of these otherwise similar functions in this section of the Level.

Discussion

Except for their names, the DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions are identical in syntax – the “behind the scenes action” embedded in the operation of each is indicated by the period suffix of its name.

Syntax

Syntactically, the parameters you provide are the same, and specified within the parentheses to the right of each function’s name, as you can see in this section:

NEXTDAY()

NEXTDAY(<dates>)    

NEXTMONTH()

NEXTMONTH(<dates>)

NEXTQUARTER()

NEXTQUARTER(<dates>)

NEXTYEAR()

NEXTYEAR(<dates>)

The single parameter for each function is:

• Dates – A column containing date expression

Further Considerations

• The dates argument 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.
• A Boolean expression filter is an expression that evaluates to TRUE or FALSE. Relevant rules include the following:
  • They can reference columns from a single table.
  • They cannot reference measures.
  • They cannot use a nested CALCULATE() function.
• They cannot use functions that scan or return a table unless they are passed as arguments to aggregation functions.
• They can contain an aggregation function that returns a scalar value.
• The “Next” functions are not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
• The NEXTYEAR() function affords a string-literal year_end_dateparameter that is required for accurate operation within a fiscal year environment.  While this sort of complication is often omitted in other articles where such parameters are needed, I find that performing my articles using a fiscal year example allows practical examination use of the parameter, and adds utility to the Level covering the function. If you happen to be looking to implement the function in a calendar year environment, you can simply omit the year_end_date parameter that you will see in use within this Level.

Return Value

According to the Data Analysis Expressions (DAX) Reference, the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR()  functions each return a table that contains a column of all dates from the following, respectively, based upon the first date specified in the Dates column in the current context:

You’ll “meet” the “NEXT” functions in the Practice section that follows.  As I’ve noted, they are quite similar (with the exception of the NEXTYEAR() function), so introducing them this way makes it easier for you cover more in less time.

Practice

You can easily gain an understanding of each of the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR()  functions by putting them into action within a compact sample data set like the one 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 perform correctly through viewing the results you obtain via a “self-checking” scenario. Because the four functions with which you will be working in this section are virtually identical in mechanical operation (again, with the possible exception of NEXTYEAR() function, within your own environment), you can combine your practice efforts with the group into one Matrix visual.

You’ll begin with a simple reporting requirement: to present results of each of the “Next” functions based upon some data you pull into a simple Power Pivot report.   Keep in mind the versatility that is possible within the Power BI application to design visualizations so that you can essentially perform “custom” queries at runtime – slicers, for example, might be added to narrow analysis to various years, various locations, and combinations of these and many other dimensional parameters.  Use your imagination to make your reports offer multiple possible uses to the information consumers you support!

Once you have a Matrix visual in place for this purpose, you will create six measures to demonstrate the operation of each of the DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions – six because you’ll use NEXTDAY() to generate a date, together with a Total Sales calculation for that date, as well as an “extra” NEXTYEAR() calculation to demonstrate a secondary year-end option.  Adding these measures to the same Matrix will provide instant visual verification that the functions deliver the appropriate data (in this case values) in the manner that you expect.  The fact you’re presenting the calculation output in a Matrix visualization will allow you to generate and examine multiple outputs in a single view -it is my hope that the option to drill up / down via the Matrix will assist you as much it does me in focusing upon the action at specific levels of the data hierarchy, either together or separately.

Practice Exercise: Illustrate the Operation of the DAX “NEXT” Functions through Individual Measures You Create

To prepare for some hands-on exposure to the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions you’ll create a simple, seven-column Matrix visualization, initially - to display Date (on the rows of the Matrix), and Total Sales amount, Next Day (the date), and Next Day Total Sales, Next Month Total Sales, Next Quarter Total Sales, and Next Year Total Sales amounts  (two options). I omit “Total” in the measure names to compress the size of the matrix. The matrix will be filtered to approximate the timeframe of “the full 2018 through 2019 Fiscal Years and beyond” (July 1, 2017 to June 30, 2019 and beyond) data available in the sample model. This Matrix will resemble the one partially depicted below.

Illustration 4:  Your Goal with the Practice Matrix (Partial View) …

NOTE: While I walk through the atomic steps of constructing the sample Matrix in the following sections, you may be new to the Power BI Matrix and want to review its general operations. Here is a good place to start for more details: https://learn.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

Install the Matrix Visualization to Display the Date Rows

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 tab labelled Exercise Examples), dropping it in the location there.
2. Right-click, and rename, the tab, as shown. (I called mine "Next" Functions, but name yours as you prefer.)

Illustration 5:  Renaming the New Practice Tab …

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

Illustration 6:  Create a New Matrix on the Canvas

5. Ensuring that the new matrix visualization is selected, add the following table.row pairs (Data tab) to the Rows section, underneath the Visualizations collection:
   • Fiscal Year
   • Fiscal Quarter
   • Month
   • Date

Your additions should appear, within the Visualizations tab - Rows section, as shown.

Illustration 7:  Values Additions in the Rows Section of the Visualizations Tab

 While it might be accomplished at any time, you can make the top (and what will be rendered as “leftmost” in the new Matrix header) label appear as something more versatile than “Fiscal Year,” as the Matrix visualization will support expanding / collapsing row headers.  It’s a good idea to keep in mind what the intended consumers of the report will see once the report is published.  Here, I will modify the label to be a more generic “Date.”  You can, of course, make it something else if you want. 

6. Right-click the Fiscal Year entry in the Rows
7. Select Rename for this visual from the context menu that appears.

Illustration 9:  Newly Added Matrix – Expanded View

Next, you can add a measure that already exists in the model data set.  Total Sales will be used throughout the exploration of the “Next” functions in this Stairway level.

8. Ensuring that the new matrix visualization is selected, add the following table.column pair to the Values section, underneath the Visualizations collection:
   • Total Sales

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

Illustration 10:  Values Additions in the Fields Tab …

9. With the new Matrix selected, click the Format visual (middle) button atop the Visualizations pane, as depicted.

Illustration 11:  Click the Format Visual Button …

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

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

Illustration 12:  Suggested Format Adjustments (for Readability)

Now it’s time to gain some exposure to the DAX “Next” functions.  To do so, you’ll create measures in the practice section that follows.

Create Measures to Support the Next() Functions

You’ll create several measures to demonstrate each of the NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions in action, within an extension of the Matrix visualization you have created above. This will give you the benefit, once again, of creating “self-checking” measures within visualizations in Power BI – a convenient way to afford oneself “reasonableness” checking (and potentially more). The cumulative values delivered by measures of a given time interval (Day, for example) can be compared to the output of corresponding, higher-level (with regard to the Date dimension) measures in adjacent columns to “spot check” accuracy and completeness of the measure operation.

NOTE 1:  As mentioned elsewhere within this subseries, you will see solution measures (their names are preceded by a “z_”) in the _Measure table of the Data pane. These measures are used within the EXERCISE EXAMPLES tab 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 borders 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 (based upon the Level in which you are currently working) through the last Level of this subseries.

NEXTDAY() Function

The more (likely) common use of NEXTDAY() is to return a given value from the next day (when used within a calculation function).  NEXTDAY() alone returns the date of the next day, as you will see.

First, you’ll create a measure to generate the value of the “next day” for the context of the given row in the Matrix you have created.  You will then create a measure whose output will be the Total Sales amount based upon that same Date context. Because you have already pulled Date data, with each respective hierarchical level in its own column, you’ll be able to examine the different “Next Dates” returned by the function within various contexts simply by (visually) eliminating columns.  This will allow you to easily compare the effects of row context upon the date generated by the new measure.  For now, you’ll start with what, in effect, will be the granular Date level context.

in accordance with my own approach to learning and working with functions, as well as within measures I create based upon those functions, you’ll examine the output of each function in an environment where it will be easy to verify accuracy / completeness.  In the business reporting environment simple date outputs would be redundant, in most cases; I’m showing the dates themselves here for learning purposes.

To create the “Next Date” measure with the context of the Date level, 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 13:  Click New Measure to Begin Design …

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

Next Day = 
      NEXTDAY('Date'[Date])

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

Illustration 14:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the next date” that corresponds with the current row context (that is, “the date (Date column) that inhabits the row upon which the measure rests.” That means that the date of the Date itself would be returned, say, for a row with the Date context of the same date.  (Again, it may not seem useful to generate this, but the point is to illustrate context.)

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 15:  Format the New Measure …

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

Illustration 16:  Add the New Measure to the Matrix Visualization

The new Next Day measure is added to the Values section of the Visualizations pane, where it appears as depicted.

Illustration 17:  New Measure in the Values Section

The Matrix visualization appears, at this point, as partially shown – Date column sorted in ascending order, and drilled to the Day level.

Illustration 18:  Matrix Visualization at this Stage …

As you can see, the Next Day measure appears to be working as expected.  Each is one day later than the Date on the far left of the row on which it resides. This is expected to be the case, because Date is the lowest level in the Date hierarchy’s selection into the report, and thus you have set the row context at the date level.  Therefore, when we use NEXTDAY() in the calculation underlying Next Day Sales, the Date level drives the “next date” delivered by the calculation.  (You’ll see the effect of changing the row context to another level of the Date hierarchy shortly. Again, you won’t likely have significant occasion for using NEXTDAY() at the literal Date level; my point here is to focus upon context.)

Next, you’ll see how to exploit the date output of the DAX NEXTDAY() function to create a measure that generates the Total Sales corresponding to the “date of the next day.”

Generate the Total Sales for the “Next Date” using NEXTDAY()

Next, you’ll create a measure to generate the value of the Total Sales as of the “next day” at the Date level – meaning, of course, the Total Sales amount of the Date itself, once again, for the sake of initial illustration.

1. Click the ellipses (“…”) to the right of the _Measure table within the Data
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:

Next Day Sales = 
   CALCULATE(
      SUM(Sales[Sales Amount]), 
   NEXTDAY('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 19:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the next date that corresponds to the current row context” (“where you are,” in this case, with regard to context in the Date hierarchy – which is dictated by the Date row upon which the DAX is performed).

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 20:  Format the New Measure …

6. Add the new measure to the Matrix visualization by clicking the checkbox to the left of the measure in the Fields. The new Next Day Sales measure is added to the Values section of the Visualizations pane. The Matrix visualization appears, at this point, as shown.

Illustration 21:  Matrix Visualization with New Measure Addition …

As you can see, both the Next Day and the new Next Day Sales measures are operating as expected:  The “Total Sales amount as of the next Date” (the row context), like the “Date of the next Sales”, shifts with each new day to match the changing dates (and continues to work in this fashion throughout the Matrix).  Again, because we created the two measures to map the given Date, the shift occurs, row-by-row, to deliver the matching date and the value of Total Sales for that date, respectively. You will see, as you proceed through the next sections, how the additional “Next” functions manage changes to levels in the date hierarchy.

Next, you’ll gain some exposure to the NEXTMONTH() function, and create measures to contain the function and to deliver output to the matrix we have set up already.  Many steps will be quite similar to those you have taken with NEXTDAY(), just for the next month instead of the day.

NEXTMONTH() Function - Generate the Total Sales for the “Next Month” using NEXTMONTH()

 Next, you’ll create a measure whose output will be the Total Sales amount, based upon the same Date context, but this time within the next month. In this, as well as the other cases that lie ahead in this Level, we will find the capability to transition between levels of the Date hierarchy particularly convenient: because you have already pulled Date data into the Matrix, you’ll be able to see aggregates by Date hierarchy level when you expand / collapse the appropriate row headers.

To create the “Next Month Sales” measure with the context of the Date level, take the following steps:

1. Click the ellipses (“…”) to the right of the _Measure table within the Data
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:

Next Month Sales = 
   CALCULATE(
      SUM(Sales[Sales Amount]), 
   NEXTMONTH('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 22:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the next month that corresponds to the current row context” (again, dictated by the Date row upon which the DAX is performed). So NEXTMONTH() behaves consistently with the operation you saw with NEXTDAY() (just within the context of another hierarchical level of Date) in the immediately preceding example.

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 23:  Format the New Measure …

6. Add the new measure to the Matrix visualization by clicking the checkbox to the left of the measure in the Data

The new Next Month Sales measure is added to the Values section of the Visualizations pane.

The Matrix visualization appears, with the rows collapsed to the Month level (click the “-“ sign to the left of the given month to “roll up” the underlying date-level rows and present the monthly aggregates) at this point, as shown.

Illustration 24:  Matrix Visualization with New Measure Addition …

As you can see, the new Next Month Sales measure is operating as expected:  The “Next Month Sales” rolls up to the amount shown in the Total Sales column, one row (“next month” context) below the row of the monthly total.

Next, you’ll gain some exposure to the NEXTQUARTER() function, and create a measure to contain the function and to deliver output to the matrix.  The steps, of course, will be essentially the same as those you have taken with NEXTDAY() and NEXTMONTH(), just for the next quarter this time.

NEXTQUARTER() Function - Generate the Total Sales for the “Next Quarter” using NEXTQUARTER()

Next, you’ll create a measure whose output will be the Total Sales amount based upon the same Date context, but this time within the next quarter. In this, as well as the other cases that lie ahead in this Level, we will continue to find the capability to transition between levels of the Date hierarchy convenient, and to appreciate being able to switch between hierarchical aggregates by expanding or collapsing the appropriate row headers.

To create the “Next Quarter” measure with the context of the Date level, take the following steps:

1. Click the ellipses (“…”) to the right of the _Measure table within the Data
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:

Next Quarter Sales = 
   CALCULATE(
      SUM(Sales[Sales Amount]), 
   NEXTQUARTER('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 next quarter that corresponds to the current row context” (that is, “where you are,” once again, with regard to context in the Date hierarchy – which is dictated by the Date row upon which the DAX is performed). So, we see that NEXTQUARTER() behaves consistently with the operation we witnessed with NEXTMONTH() (just within the context of another hierarchical level of Date) in the immediately preceding example.

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, as you have done with other measures earlier.

Illustration 26:  Format the New Measure …

6. Add the new measure to the Matrix visualization by clicking the checkbox to the left of the measure in the Data

The new Next Quarter Sales measure is added to the Values section of the Visualizations pane.

The Matrix visualization appears, with the rows collapsed to the Quarter level (click the “-“ sign to the left of the given quarter to “roll up” the underlying lower-level rows and present the quarterly aggregates) at this point, as shown.

Illustration 27:  Matrix Visualization with New Measure Addition …

As you can see, the new Next Quarter Sales measure is operating as expected:  Next Quarter Sales agrees to the amount shown in the Total Sales column, one row (“next quarter” context) below the row of the quarterly total.

Next, you’ll gain some exposure to the NEXTYEAR() function, and create a measure to contain the function and to deliver its output to the matrix.  The steps, as one would expect, will be essentially the same as those you have taken with NEXTDAY(), NEXTMONTH(), and NEXTQUARTER() functions, just for the next year this time.

NEXTYEAR() Function - NEXTYEAR() for a Calendar Year End

Finally, you’ll create a measure to accumulate the value of the Total of Sales to for the Next Year – in cases where you’re working with a Calendar Year End. The steps – and the results you can expect – mirror those of your experience with each of the “Next” functions above, only they will be from the perspective of years versus quarters, months, or days.

You’ll create a measure whose output will be the Total Sales amount based upon the same Date context, but this time within the next calendar year. Once again, we will continue to extend the convenience of transition between levels of the Date hierarchy – this time to the year level. This will, of course, grant information consumers of the new Matrix the ability to expand their ability to jump instantly between hierarchical aggregates to include the Year level of the Date hierarchy.

To create the “Next Year” measure with the context of the Date level, take the following steps:

1. Click the ellipses (“…”) to the right of the _Measure table within the Data
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:

Next Year Sales (Calendar Year End = 
   CALCULATE(
      SUM(Sales[Sales Amount]), 
   NEXTYEAR('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 28:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the next year that corresponds to the current row context” (again, reflecting your positioning, with regard to context, in the Date hierarchy / Date row upon which the DAX is performed). So, we see that NEXTYEAR() behaves consistently with the operation we witnessed with NEXTQUARTER() and NEXTMONTH() (differing, in this case, only with the context of another hierarchical level of Date) in the immediately preceding example.

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, as you have done with other measures earlier.

Illustration 29:  Format the New Measure …

6. Add the new measure to the Matrix visualization by clicking the checkbox to the left of the measure in the Data

The new Next Year Sales (Calendar Year) measure is added to the Values section of the Visualizations pane.

The Matrix visualization appears, with the rows collapsed to the Year level (click the “-“ sign to the left of the given quarter to “roll up” the underlying date-level rows and present the yearly aggregates) at this point, as shown.

Illustration 30:  Matrix Visualization with New Measure Addition …

As you can see, the new Next Year Sales (Calendar Year End) measure values, unlike those of the previously created measures, do not agree to the values as shown in the Total Sales column on the far left for the respective years.  This is the expected behavior of the Next Year Sales (Calendar Year End) measure, of course, because the new measure is rendering the value based upon the default assumption of the NEXTYEAR() function that it is to render the Calendar year value, versus the actual Fiscal year value.  We have seen this behavior before, in other Time Intelligence functions’ operation at the year level – as was the case within those Levels, we can manage the correct calculation for either the Calendar year default (as above), or we can direct the recalculation of affected measures by appending the Fiscal year end date within the function.  This directs DAX to “reset” the Calendar year default of 12-31-xxxx to the appropriate Fiscal year end date. You’ll be creating a measure for Next Year Sales (Fiscal Year End) in the next section of the practice session.

NEXTYEAR() for a Fiscal Year End

You’ll reach the end of this Level with the creation a measure to accumulate the value of the total of sales to date for the Next Year once again – this time for cases where you’re working with a Fiscal Year End. The steps – and the results you can expect – mirror those of your experience with each of the “Next” functions above, only they will require a small, extra step to add syntax to the NEXTYEAR() function to tell DAX that the default Calendar year end value of 12-31-xxx needs to be overridden with a string you supply for the Fiscal year end.

Finally, you’ll create a measure whose output will be the Total Sales amount based upon the same Date context, but this time for the next fiscal year. This will, of course, grant information consumers the same ability you’ve delivered with previous measures, to expand their ability to jump instantly between hierarchical aggregates to include the Year level of the Date hierarchy.  (We will leave the Calendar year equivalent of this activity in place, for comparative purposes.)

To create the “Next Year” measure with the context of the Date level, take the following steps:

1. Click the ellipses (“…”) to the right of the _Measure table within the Data
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.

Next Year Sales (Fiscal Year End) = 
   CALCULATE(
      SUM(Sales[Sales Amount]), 
   NEXTYEAR('Date'[Date], "2018-06-30")
   )

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

Illustration 31:  Commit the New Measure …

With this calculation, you’re once more creating a measure to return “the Total Sales as of the next year that corresponds to the current row context” (again, reflecting your positioning, with regard to context, in the Date hierarchy / Date row upon which the DAX is performed). The only difference in this calculation and the one that immediately precedes it is that we are setting the DAX NEXTYEAR() function to operate on a fiscal year end, versus the default calendar year end of 12-31-xxxx.  Other than the difference in year end specification, we’ll we see that NEXTYEAR() behaves consistently with the operation we witnessed with NEXTQUARTER() and NEXTMONTH() (differing, in this case, only with the context of another hierarchical level of Date) in the immediately preceding example.

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, as you have done with other measures earlier.

Illustration 32:  Format the New Measure …

6. Add the new measure to the Matrix visualization by clicking the checkbox to the left of the measure in the Data

The new Next Year Sales (Fiscal Year) measure is added to the Values section of the Visualizations pane.

The Matrix visualization appears, with the rows collapsed to the Year level (click the “-“ sign to the left of the given year to “roll up” the underlying date-level rows and present the yearly aggregates) at this point, as shown.

Illustration 33:  Matrix Visualization with New Measure Addition …

As you can see, the new Next Year Sales (Fiscal Year End) measure values agree to the values as shown in the Total Sales column on the far left for the respective years.  This is the expected behavior of the Next Year Sales (Fiscal Year End) measure, of course, because your insertion of the optional string into the function directs the DAX engine to “reset” the calendar year default of 12-31-xxxx to the Fiscal year end date you have supplied.

You now have a set of simple Matrix visualizations that demonstrate the assembly and operation of the DAX NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions.

Summary

This Level of the Stairway to DAX and Power BI is part of a DAX Time Intelligence subseries, within which I typically 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 NEXTDAY(), NEXTMONTH(), NEXTQUARTER() and NEXTYEAR() functions, you focused upon putting the respective function to work within the construction of a measure that demonstrated using it for reporting at 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 the examples you undertook, you generated a self-validating results dataset to ensure accuracy and completeness of the results output by the measure you constructed to contain each respective function.