Perhaps the biggest mistake people make when attempting to use DAX's time intelligence functions is to not utilize a related date table for the dates parameter of the functions. Not utilizing a related table of dates can cause unexpected results. To see how things can go awry, perform the following steps:
- Create the following measure:
Opening Balance 2 = OPENINGBALANCEMONTH(SUM([Value]),'R01_Sales'[Date])
- Place the Opening Balance 2 measure in both the Table visualization and the Matrix visualization created earlier.
- Delete the relationship between the R01_Sales and R01_Calendar tables.
Note that the Opening Balance 2 measure values are all blank in the Matrix table, but are correct in the Table visualization. As already stated, you can get unexpected results when not using a related date table.