Using the OPENINGBALANCEYEAR function

To determine the total number of crashes at the beginning of each year, you can use the built-in functions to calculate opening balances. In this recipe, you will create a semi-additive measure to calculate the number of crashes at the beginning of each year. This will enable you to find out the total number of crashes each year and determine trends.

How to do it...

1. Open the Model.bim to the CRASH_DATA_T table.
2. In the measure creation area, click on an empty cell to create a measure to return the opening year balance:

           OpeningYear:=OPENINGBALANCEYEAR( COUNT 
               (CRASH_DATA_T[CASE_NUMBER]), 
           Calc_Date_T[CRASH_DATE]) 
   

3. Then press Enter to create the measure:
   

4. Now deploy the solution to the server and switch to SQL Server Management Studio to view the results. Browse the model and select the YEAR, Month_Name, Records, OpeningMonth, and OpeningYear. Set the slicer to use the Calc_Date_T year to include 2016 and 2015 to limit the results...