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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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.

I’ve said repeatedly that 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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: “Start 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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, “start” 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 July 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 Power BI 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 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.

Open Power BI Desktop. 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

Navigate to the file you have downloaded.

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

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.

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.

“Start of Period”: The DAX STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() Functions

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

Discussion

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

Syntax

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

STARTOFMONTH()

STARTOFMONTH(<dates>)

STARTOFQUARTER()

STARTOFQUARTER(<dates>)

STARTOFYEAR()

STARTOFYEAR(<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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() functions each return “a table containing a single column of date values.”

Practice

Next, you’ll put the “Start 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 for 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() through Individual Measures You Create

To prepare for some hands-on exposure to STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR(), 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 Samples 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 "Start 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 above 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() functions.  To do so, you’ll create measures in the practice section that follows.

Create Measures to Support the STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() Functions

You’ll create a measure to demonstrate each of the STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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 the preceding levels of 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.

Generate the “Start of the Month” Date with STARTOFMONTH()

 First, you’ll create a measure to generate the value of the first 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 “Start 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:

STARTOFMONTH =
   STARTOFMONTH('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 first 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 first 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 STARTOFMONTH measure is added to the Columns section of the Visualizations pane.

7. Click the new STARTOFMONTH measure in the Columns section, and drag it the position With the table on the canvas selected, 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 STARTOFMONTH measure appears to be working as expected.  For each date row, the first date of the month, whatever the month is indicated to be in the Date field, is generated.  (If you scroll further down the Table, you will note that the date in STARTOFMONTH shifts to first of the next month with the first 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 beginning of the month shown in STARTOFMONTH.

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

Generate the Total Sales at the “Start of the Month” Date using STARTOFMONTH()

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

SOM SALES = 
   CALCULATE ( 
      [Total Sales], STARTOFMONTH('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 first 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 first 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 SOM 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 STARTOFMONTH measure, the new SOM SALES measure is operating as expected:  The “Start of Month” sales value, like the “Start 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 STARTOFQUARTER() 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 STARTOFMONTH() function,  I’ll be showing illustrations only where differences occur between this and the other “to date” functions.

Generate the “Start of the Quarter” Date with STARTOFQUARTER ()

 First, you’ll create a measure to generate the value of the first 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 “Start 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:

STARTOFQUARTER =
   STARTOFQUARTER('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 first 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 first date in the second quarter. (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 STARTOFQUARTER measure is added to the Columns section of the Visualizations pane.

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

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

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

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

As you can see, the STARTOFQUARTER measure appears to be working as expected.  For each date row, the first date of the quarter, whatever the month is depicted to be in the Date field, is generated.  Moreover, the first 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 STARTOFQUARTER shifts to last of the month with the first 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 beginning of the month shown in STARTOFQUARTER, as we noted earlier while working with the STARTOFMONTH() function.

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

Generate the Total Sales at the “Start of the Quarter” Date using STARTOFQUARTER()

As I’ve stated earlier, the date outputs themselves would likely be redundant in most cases, at least at the visualization level, but 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:

SOQ SALES = 
   CALCULATE ( 
      [Total Sales], STARTOFQUARTER('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 first date of the quarter that corresponds to the current row context – which is dictated by the date row upon which the DAX is performed. That means that the first 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 SOQ 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 STARTOFQUARTER measure, the new SOQ SALES measure is operating as expected:  The “Start of Quarter” sales value, like the “Start 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 STARTOFYEAR() 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 STARTOFQUARTER() function, I’ll be showing illustrations only where differences occur between this and the other “to date” functions.

Generate the “Start of the Year” Date with STARTOFYEAR() - STARTOFYEAR() for a Calendar Year End

This time you’ll create a measure to generate the value of the first 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 “Start 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:

STARTOFYEAR =
   STARTOFYEAR('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 first 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 first 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 STARTOFYEAR measure is added to the Columns section of the Visualizations pane.

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

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

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

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

The STARTOFYEAR measure appears to be working as expected.  (That is, except in the case of the first year in the model, 2017, because the model’s Date table is based upon a fiscal year, beginning on 7/1/2017. We therefore see “7/1/2017 appearing as the start of year for the first year, because data goes back no further than date in the model.) Other than for the first year, for each date row, the first date of the year is generated, whatever year is in the Date field.

NOTE: We will look specifically how to manage the STARTOFYEAR() function for a fiscal year in the next, and final, subsection.

As we have seen throughout our examination of the DAX “Start of Period” functions as a group, the Total Sales depicted in each row corresponds to the sales of the indicated Date, perhaps obviously, and not the date of the beginning of the month shown in STARTOFYEAR.

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

Generate the Total Sales at the “Start of the Year” Date using STARTOFYEAR()

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

SOY SALES = 
   CALCULATE ( 
      [Total Sales], STARTOFYEAR('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 first 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 first 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 SOY 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 STARTOFYEAR measure, the new SOY SALES measure is operating as expected:  The “Start of Year” sales value, like the “Start of Year” date, shifts at the beginning of the new year (and continues to work in this fashion throughout the Table).

STARTOFYEAR() for a Fiscal Year End

Adjusting the STARTOFYEAR 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 STARTOFYEAR 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 STARTOFYEAR(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 STARTOFYEAR (Fiscal) measure in the Columns section of the Visualizations pane, and drag it to be between the STARTOFYEAR and Total Sales measures, as depicted.

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

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

Illustration 30:  Table Visualization with Last Measure Addition …

The STARTOFYEAR (Fiscal) measure appears to the right of the original STARTOFYEAR (the Calendar Year version), unsurprisingly displaying identical values to its predecessor STARTOFYEAR measure, as all remains the same 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 STARTOFYEAR('Date'[Date], to enable the <YearEndDate> parameter (similar to the one we encounter with the DATESYTD() function).  The pseudocode looks like this:

STARTOFYEAR(<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 STARTOFYEAR (Fiscal) measure, within the STARTOFYEAR('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 STARTOFYEAR() function, after the comma you have added.

The DAX expression for the new measure appears as shown:

STARTOFYEAR (Fiscal) = 
   STARTOFYEAR('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 STARTOFYEAR (Fiscal Year) Measure

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

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

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

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

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

You now have a simple Table visualization that demonstrates the assembly and operation of the DAX “Start of Periods” functions.  Moreover, this visual contains examples for how to manage both Fiscal and Calendar years with one of the functions, STARTOFYEAR().  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. Here 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 STARTOFMONTH(), STARTOFQUARTER(), and STARTOFYEAR() 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 generated by the measure you constructed to contain the respective function.