Finding the last date with data
In this recipe we're going to learn how to find the last date with data for a particular combination of members in the cube. We'll start with a general calculation, not dependent on the time context, and later show how to make it time-sensitive, if required so.
Getting ready
Open
SQL Server Data Tools (SSDT) and then open Adventure Works DW 2012 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's a value in that property. Column FullDateAlternateKey, of the Date type, 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 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.
In this example...
