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 FIRSTDATE() and LASTDATE() 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, FIRSTDATE() and LASTDATE() return the first and last dates, respectively, within an active filter context.  As you’ll see in this Level, you can use the FIRSTDATE() and LASTDATE() functions to find the first and last occurrences of a transaction – even when limiting the returned occurrence even further by other criteria, such as a specified location, product or other criteria.

As we’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 FIRSTDATE() and LASTDATE() 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 first and / or last occurrences of a given transaction or activity are the focus of a report or other query.

Illustration 1: “First and Last Dates” 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 table visualization you’ll construct.  Combining the functions within the same table will allow comparison and contrast. This approach means that Time Intelligence functions can be grouped into logical Stairway Levels, and that multiple functions can, in many cases, be explored in 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.

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 FIRSTDATE() and LASTDATE() 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 “first” and “last” data, respectively, within the active filter context, in helping 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 Level reflect the December 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 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 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 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, as desired, along the upper left frame of the Power BI Desktop canvas, as desired, to become familiar with the sample model.

“First and Last Period Dates”: The DAX FIRSTDATE() and LASTDATE() Functions

The operation, and many potential uses, of the DAX FIRSTDATE() and LASTDATE() functions are very similar, except for the fact that the former function focuses upon the first date within a specified date period, and the latter upon the last date within a specified period. (And, as we have seen in other grouped functions in DAX, the purpose of each function is conveniently embedded within its name).  I combine the explanation of these two otherwise similar functions in this Level.

Discussion

Except for their names, the DAX FIRSTDATE() and LASTDATE() functions are identical in syntax – the “behind the scenes action” embedded in the operation of each is indicated by the “First” or “Last” prefix of its name.

Syntax

Syntactically, the parameters you provide are the same, and specified within the parentheses to the right of each of the FIRSTDATE and LASTDATE names, as you can see in this section:

FIRSTDATE()

FIRSTDATE(<dates>)

LASTDATE()

LASTDATE(<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 (post-September 2021 releases).
• They can contain an aggregation function that returns a scalar value.

Return Value

According to the Data Analysis Expressions (DAX) Reference, the FIRSTDATE() and LASTDATE() functions return, respectively, “the first date in the current context for the specified column of dates” and “the last date in the current context for the specified column of dates.”

Practice

You can easily gain an understanding of the FIRSTDATE() and LASTDATE() functions by putting them into action with 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. As the two functions concerned in this section are virtually identical in mechanical operation, you can combine your practice efforts with both into one Table visual.

You’ll begin with a simple reporting requirement: to present the earliest (“first”) and latest (“last”) date of sales in a given month, combined with the value of the Total Sales values for each of those dates. (As it happens, the model data presents a largely simple scenario of sales for every day of each month, so, unsurprisingly, the “first and last dates of sales” for each month land upon the first and last date, respectively, of each month calendar-wise.) In this practice set, you’ll also ultimately generate the first and last sales totals for other calendar periods over the full fiscal years contained, as you will see.

Once you have a Table visual in place for this purpose, you will add two measures to demonstrate the operation of each of the DAX FIRSTDATE() and LASTDATE() 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.  You will then add a calculation to generate the total sales as of each returned date, to demonstrate the utility of the date value in providing context to generate the associated Total Sales value as of each of the determined dates.

Practice Exercise: Illustrate the Operation of FIRSTDATE() and LASTDATE() through Individual Measures You Create

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

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

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 tab labelled Exercise Samples), dropping it in the location there.
2. Right-click, and rename, the tab, as shown. (I called mine FIRSTDATE() and LASTDATE() 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):
   1. Values > Values - Font size of 12
   2. 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 FIRSTDATE() and LASTDATE() functions.  To do so, you’ll create measures in the practice section that follows.

Create Measures to Support the FIRSTDATE() and LASTDATE()Functions

You’ll create a measure to demonstrate each of the FIRSTDATE() and LASTDATE() functions in action, within a duplicate of 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 – a convenient way to afford oneself “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 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 within the last Level of this subseries.

FIRSTDATE() Function

Generate the “First Date” in a Current Context for a Specified Column of Dates with FIRSTDATE()

 First, you’ll create a measure to generate the value of the “first date of the current Date.”  You will then create a measure whose output will be the sales amount based upon that 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 “First Dates” returned by the function within various contexts simply by eliminating columns, as we shall see.  This will allow you to visually compare the effects of row context upon the date generated by the new measure.  For now, we’ll start with what, in effect, will be the 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 these date outputs themselves would be redundant in most cases.  I’m showing the dates themselves here just for learning purposes.

To create the “First 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 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:

Sales 1st Date = 
   FIRSTDATE ( 'Date'[Date] )

2. 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 first 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 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 Sales 1^st Date measure is added to the Columns section of the Visualizations pane.

7. Click the new Sales 1^st Date 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.

As you can see, the Sales 1^st Date measure appears to be working as expected.  It is identical to the Date to its immediate left. 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 FIRSTDATE() in the calculation underlying Sales 1^st Date, the Date level drives the “first date” delivered by the calculation: The “first date” that is driven at the Date level, in this case, is the selfsame date that drives it. (You’ll see the effect of changing the row context to another level of the Date hierarchy shortly. You won’t likely have any use for using FIRSTDATE() at the literal Date level; my point here is to focus upon context.)

Next, you’ll see how to apply the date output of the DAX FIRSTDATE() function to create a measure that generates the Total Sales corresponding to the “date of the first date.”

Generate the Total Sales at the “First Date” using FIRSTDATE()

Next, you’ll create a measure to generate the value of the Total Sales as of the “first date” 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 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:

   Sales Amt 1st Day = 
      CALCULATE(
         [Total Sales], FIRSTDATE ( '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 calculation, you’re creating a measure to return “the Total Sales as of the first date that corresponds to the current row context” (that is, “where you are,” in this case, with regard to context the Date hierarchy – which is dictated by the date row upon which the DAX is performed). That means, in the present case, that the first “date of the day” would be returned, say, for a row with the Date context of a given day of a 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 Sales Amt 1st Day measure is added to the Columns section of the Visualizations pane.

7. Click the new Sales Amt 1st Day measure in the Columns section, and drag it to the position immediately above the Total Sales measure, as depicted.

Illustration 18:  Rearranging the New Measure to Place It to the Right of the New Sales 1^st Date Column …

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

Illustration 19:  Table Visualization with New Measure Addition …

As you can see, both the Sales 1^st Date measure and the new Sales Amt 1^st Day measure are operating as expected:  The “Sales Amount as of the given Date” (the row context), like the “Date of the first Sales”, shifts with each new day to match the changing dates (and continues to work in this fashion throughout the Table).  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 amount of sales for that date, respectively. You will see, after the next section, how the function adjusts automatically to changes to levels in the date hierarchy.

Next, you’ll gain some exposure to the LASTDATE() function, and create measures to contain the function and to deliver output to the table we have set up already.  Many steps will be quite similar to those you have taken with FIRSTDATE(), just for the last date instead of the first.

LASTDATE() Function

Generate the “Last Date” in a Current Context for a Specified Column of Dates with LASTDATE()

First, you’ll create a measure to generate the value of the last date of the current Date.  You will then create a measure whose output will be the sales amount based upon that 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 “Last Dates” returned by the function within various contexts simply by eliminating columns.  As was the case in the FIRSTDATE() section above, this will allow you to visually compare the effects of row context upon the date generated by the new measure.  Again, for now, you’ll start with what, in effect, will be the Date level context. Once you’ve set up a working pair of measures that are based upon LASTDATE(), you be able to see how the function’s context can shift with date hierarchy.

To create the “Last Date” measure within 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, once again.
3. Type (or cut and paste) the following into the Formula bar for the new measure that appears:

Sales Last Date = 
   LASTDATE ( 'Date'[Date] )

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

Illustration 20:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the last 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.  (It may not seem useful, as I’ve noted in similar situations before, 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.
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 21:  Add the New Measure to the Table Visualization

The new Sales Last Date measure is added to the Columns section of the Visualizations pane.

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

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

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

Illustration 23:  Table Visualization at this Stage …

As you can see, the Sales Last Date measure appears to work as expected.  It is identical to the Date to its left. This, again, is expected to be the case, because Date is the lowest level in the Date hierarchy’s selection into the report, and thus the row context is established at the date level.  Therefore, when we use LASTDATE() in the calculation underlying Sales Last Date, the Date level drives the “last date” delivered by the calculation: The “last date” that is driven at the Date level is the selfsame date that drives it. (As noted in the earlier section, you won’t likely have any use for employing LASTDATE() at the literal Date level; my point, again, is to focus upon context. You’ll see the effect of changing the row context to another level of the Date hierarchy shortly.)

Next, you’ll see how to apply the date output of the DAX LASTDATE() function to create a measure that generates the Total Sales corresponding to the “date of the first date.”

Generate the Total Sales at the “Last Date” using LASTDATE()

To follow the path you took earlier, in working with FIRSTDATE(), you’ll create a measure to generate the value of the Total Sales as of the “last date” 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 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:

Sales Amt Last Day = 
   CALCULATE(
      [Total Sales],
      LASTDATE ( '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 24:  Commit the New Measure …

With this calculation, you’re creating a measure to return “the Total Sales as of the last date that corresponds to the current row context” (that is, “where you are,” in this case, with regard to the context of the Date hierarchy – which is dictated by the date row upon which the DAX is performed). That means, in the present case, that the last “date of the day” would be returned, say, for a row with the Date context of a given day of a 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.
6. Add the new measure to the Table visualization by clicking the checkbox to the left of the measure in the Fields

The new Sales Amt Last Day measure is added to the Columns section of the Visualizations pane.

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

Illustration 25:  Rearranging the New Measure to Place It to the Right of the New Sales Last Date Column …

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 Sales Last Date measure, the new Sales Amt Last Day measure is operating as expected:  The “Sales Amount as of the given Date” (the row context), like the “Date of the last Sales,” shifts with each new day to match the changing dates (and continues to work in this fashion throughout the Table).  Again, because we created the two measures to map the given Date, they shift, row-by-row, to deliver the matching date and the amount of sales for that date, respectively. You will see, again, after the next section, how the function adjusts automatically to changes to levels in the Date hierarchy.

Next, you’ll gain some exposure to the effects of shifting context upon the measures you’ve created – and achieve a broader understanding of the power of the two Time Intelligence functions you’ve come to know in this Level of the Stairway.

Shifting Context with the DAX FIRSTDATE() and LASTDATE() DAX Functions

Now that you have gained some exposure to the basic use of FIRSTDATE() and LASTDATE(), you’ll next see the basics for shifting context of the functions to align with differing levels of the Date hierarchy. To save some time, you’ll compare your existing table, which presents the functions working at the Day level of the Date hierarchy, with an identical table within which you selectively remove columns to mechanically shift Date context – and therefore drive the output of the DAX calculations you have put in place.

You can get started by creating a “lab” table, identical to the one you have built in the foregoing sections, by taking the following steps.

1. “Slim down” the size of your existing table as much as is practical, first and foremost by:
   1. Decreasing Font Size in both the Values and Columns sections of the Format pane for the existing table.
   2. Narrow the columns in the Table.
2. Drag the existing Table visual to the upper left, freeing space for another Table on the right of the canvas, roughly as shown.

Illustration 27:  Creating Space for a “Twin” Table

3. Click the existing Table to highlight it.
4. Press CTRL+C.
5. Click to the right of the existing table, on the blank canvas.
6. Press CTRL+V to past the copied table.

The new copy of the Table appears atop the existing Table.  Leave the copy highlighted, as it appears.

7. Use the “>” key, or mouse, to drag the new Table to the right, aligning it with the pre-existing Table as shown.

You now have a parallel pair of identical Tables, as depicted. (While the new Table likely partially covers the first, this will resolve itself as you “thin out” columns in following steps.)\

Illustration 28:  A Pair of Identical Tables …

The two Tables contain, of course, identical logic within the created calculations.  As you will see, eliminating key columns of the Date hierarchy has the effect of mechanically shifting context for these calculations.

8. Ensuring that the new Table (on the right) is still selected, eliminate its Date column by deleting same in the Columns section of the Visualizations pane, as shown.

Illustration 29:  Shifting the Context to the Month Level

You have now modified the row context to the Month, versus Date, level of the Date hierarchy.  The effects are most obvious for the Sales 1^st Date and Sales Last Date calculations, which return the first and last dates, respectfully, of the corresponding rows.

Illustration 30:  Context at the Month Level …

The same effect occurs when we reset context for the rows by eliminating the table column for Month.  I show partial effects (Fiscal Years 2019 and 2020) with the below.

Illustration 31:  Context at the Quarter Level …

You now have a pair of simple Table visualizations that demonstrate the assembly and operation of the DAX FIRSTDATE() and LASTDATE() functions.  While the simplest way to verify the proper shifting of context is to examine the fact that the dates delivered map the context directly, the calculations of the respective amounts generated at the given points in time also flex as expected.

Summary

This Level 21 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 FIRSTDATE() and LASTDATE() 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 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.