Wednesday, April 8, 2015

Date Difference using MDX VBA functions

Recently I came across one project wherein I see lots of calculated measures build on the top of Date difference and I see finding number of days, number of months, number of years is very common in most of the requirements hence thought of sharing the same.

I am demonstrating samples using AdventureWorks sample;

1. If you want to calculate Days between two supplied dates

In this sample, I am passing Date dimension member and current date and finding Days between two dates supplied.

With Member [Measures].[MemberKey] As
Member [Measures].[DateFormat] as
VBA!Mid([Measures].[MemberKey],5,2) + '/' +
VBA!Mid([Measures].[MemberKey],7,2) + '/' +
Member [Measures].[CurrentDate] As
Member [Measures].[Date Diff] As
DateDiff ("d", [Measures].[DateFormat],[Measures].[CurrentDate])
{[Measures].[MemberKey],[Measures].[DateFormat],[Measures].[CurrentDate],[Measures].[Date Diff]} ON 0,
DESCENDANTS([Date].[Calendar],[Date].[Calendar].[Date],SELF) ON 1
FROM [Adventure Works]

2. Finding Number of Months 

You can find the Number of Month between two dates.

No comments:

Post a Comment