Finding the last date with data
In this recipe, we are going to learn how to find the last date with data for a particular combination of members in the cube. We will start with a general calculation, not dependent on the time context, and later show how to make it time-sensitive, if required.
Getting ready
Open SQL Server Data Tools (SSDT) and then open Adventure Works DW 2016 solution. Double-click on the Date dimension found in the Solution Explorer. Select the Date attribute and locate the property ValueColumn at the bottom of the Properties pane:
There is a value in that property. Column FullDateAlternateKey, of the DataType, is specified as the ValueColumn of the key attribute property, the Date attribute. This check is important because without that property filled correctly, this recipe won't work.
Next, start SQL Server Management Studio and connect to your SSAS 2016 instance. Click on the New Query button and check that the target database is Adventure Works DW 2016.
In this example...
