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
[Date].[Calendar].CURRENTMEMBER.MEMBER_KEY
Member [Measures].[DateFormat] as
VBA!Cdate(
VBA!Mid([Measures].[MemberKey],5,2) + '/' +
VBA!Mid([Measures].[MemberKey],7,2) + '/' +
VBA!Left([Measures].[MemberKey],4)
)
Member [Measures].[CurrentDate] As
Format(Now(),"M/d/yyyy")
Member [Measures].[Date Diff] As
DateDiff ("d", [Measures].[DateFormat],[Measures].[CurrentDate])
SELECT
{[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