Using the OPENINGBALANCEMONTH function

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

           OpeningMonth:=OPENINGBALANCEMONTH( 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 OpeningMonth. Set the slicer to use the Calc_Date_T year equal to 2016 to limit the results...