Using the CLOSINGBALANCEMONTH function

To determine the total number of crashes at the end of each month, 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 month. This will enable you to find out the total number of crashes at the end of each month 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 month balance:

           ClosingMonth:=CLOSINGBALANCEMONTH( 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, and ClosingMonth. Set the slicer to use the Calc_Date_T year equal to 2016 to limit the results:
   

The results...