Calculating today's date using the MemberValue function

The second way to calculate today's date is by using the MemberValue() function. This is something we've already used in the Finding the last date with data recipe. In case you haven't read it yet, do so before continuing with this recipe, at least the part that shows the ValueColumn property.

Getting ready

Open SQL Server Data Tools (SSDT) and then open theAdventure Works DW 2016 solution. Double-click on the Date dimension found in the Solution Explorer. Select the Date attribute and locate the ValueColumn property. It should not be empty; otherwise this recipe won't work. It should have the Date type column from the underlying time dimension table.

How to do it...

Follow these steps to calculate today's date using the MemberValue function:

1. Write and execute the following query in SQL Server Management Studio connected to the same cube mentioned earlier:

         WITH 
         MEMBER [Measures].[Caption for Today] AS 
            Format...