DYNAMIC DATE | A blog about Analytics, AWS and Azure

If you are creating scheduled reports in DAX it might be handy to add some VBA to calculate time periods automatically.

Here is an example that returns yesterday

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Date],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	FORMAT('Date'[Date], "yyyy-MM-dd") = FORMAT(NOW() - 1, "yyyy-MM-dd")
)

If you want to test a valid date in the sample database you can figure out how many days you have to remove by running this SQL “select DATEDIFF(day, getdate(), ‘2006/05/18’)”. And then subtract this number instead of “NOW() – 1”.

And if you want to have a similar query for year this can look like this

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	'Date'[Calendar Year] = INT(FORMAT(NOW() - 1, "yyyy"))
)

When I create a new cube I always add a few named sets for getting time periods automatically. These are nice to have if I have scheduled reports that should present data for today, yesterday, last week or maybe current month.

An example could be a named set that returns yesterdays date.

CREATE SET CURRENTCUBE.[Yesterdays date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);

And the named set might be used like this

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

You can of course also use this within a single query like this.

WITH SET [Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]")    -- minus 1 gives yesterdays date

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

Please note that the format of the member must be correct. In my case the Date dimensions members look like this: [Date].[Date].&[2005-01-10T00:00:00]. If you try to run these samples against the sample database you will not get any result. Since the database does not have data for the current period. If you want to test a valid date you can check how many days you have to remove by running this SQL:

select DATEDIFF(day, getdate(), '2006/05/18')

And then you can change the code “now()-1” to “now()-[The number of days the query returned]”

Here are some other useful samples

//Sample date sets.
CREATE SET CURRENTCUBE.[Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now(), "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);       
     
-- Must be used with [This Year]
CREATE SET CURRENTCUBE.[This month] AS 
StrToMember("[Date].[Month].&[" + Format(now()-1, "MM") + "]", CONSTRAINED);        

CREATE SET CURRENTCUBE.[This year] AS 
StrToMember("[Date].[Calendar Year].&[" + Format(now(), "yyyy") + "]", CONSTRAINED);      

-- Can you create a named set for current week? Last week?

A blog about Analytics, AWS and Azure

Tag Archives: DYNAMIC DATE

DYNAMIC DATE FILTER IN DAX USING VBA

Dynamic date filter in MDX using VBA