Using the CLOSINGBALANCEYEAR function

To determine the total number of crashes at the end of each year, you can use the built-in functions to calculate ending balances. In this recipe, you will create a semi-additive measure to calculate the number of crashes at the end of each year. This will enable you to find out the total number of crashes at the end of 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 closing year balance:

           ClosingYear:=CLOSINGBALANCEYEAR( 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. Include...