DYNAMIC DATE FILTER IN DAX USING VBA

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"))
)
Advertisements