How to build Dynamic Today, current month, Previous Month etc. in Formulate

1. Get Dynamic Dates Using Formulate Wizard.

   To make the access on the latest information, it often makes sense to have a dynamic member or list that will always be “today”, or “current month” etc. within the time dimension.

   One way that we can achieve this, is using the exists function on a fact where the latest date is typically the last date of processing.  In this article we will show you how to do this.

   

   Notice: this list will bring the last date that has data in the fact table. Sometimes this is exactly what we need, meaning, we want the last date with data in a certain fact (and if it was a few days ago – no problem). 
   
   We can use the same method to find the last month with data, or the previous one (just replace the Last 1 node with last 2 and first 1).

2. Using StrToMember on a date column
   In cases when the "exists" function does not help us to get the date we want.
   e.g. if we want to show yesterday’s data, in a model that may not always have data for yesterday.  In such cases we will need to use the StrToMember function. This function allows us to locate a certain member in a column or a hierarchy.
   
   The Syntax for StrToMember is: 

   StrToMember( <Hierarchy> , <Text> )

   Where the Hierarchy is the Hierarchy or column, and the text is the text of the selected member.
   
   So for example, the following will give us the country France from the Countries dimension:

   StrToMember([Customers].[Country],"France")

   If we’re trying to do it with a date column, in versions prior to 2020.27, we will need to use Epoch Time:

   StrToMember([Calendar].[Cal date],"1548028800000")

   This will bring up Jan 21st 2019. Beginning with version 2020.27, it will work with the date presented as text (in the same formatting as the column)
   
   Convert Date to Epoch Time
   The way to convert a date to epoch time will require converting a string to a date with StringToDate() and then converting to epoch time using DateToTimeStamp:

   StrToMember([Calendar].[Cal date],DateToTimeStamp(StringtoDate("2019-01-03", "yyyy-MM-dd")))

   
   The big step forward now, is to replace the StringToDate part, with the Date() function, that returns the Current Date:

   StrToMember(([Calendar].[Cal date], DateToTimestamp(Date()))

   This will return the date today on the Cal Date dimension (assuming it exists, of course)
   And If necessary – we can use the AddDays function to show yesterday, two days ago etc.

   StrToMember(([Calendar].[Cal date], DateToTimestamp(AddDays(Date(),-1)))

3. Using StrToMember on a date hierarchy
   Sometimes we will need the same calculation on a Hierarchy, rather than a column.The Name for a hierarchy member in StrToMember will be a concatenation of all the hierarchy’s levels. For example in a calendar hierarchy (Year – Quarter – Month – Date), May 2020’s unique name is:

   [Calendar].^[Calendar Hierarchy].[2020].[Q2 2020].[May 2020]

   So if we want to use StrToMember we will use:

   StrToMember([Calendar].^[Calendar Hierarchy],"[2020].[Q2 2020].[May 2020]")

   And if we want something dynamic that will always show the current month we will need to use the DateFormat() function to build the entire structure:

   StrToMember([Calendar].^[Calendar Hierarchy],"[" + DateFormat(Date(), "yyyy") +"].[Q"+ DateFormat(date(),"Q yyyy") +"].[" + DateFormat(date(),"MMM yyyy")+"]")

   For Different levels in the hierarchy, a different string will need to be adjusted.

If you have questions about these tips (or improvements) please let us know in a comment.