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.
Great Content. It will useful for knowledge seekers. Keep sharing your knowledge through this kind of article.
ReplyDeleteAzure Training in Chennai
Microsoft Azure Training in Chennai
ReplyDeleteNice blog! Thanks for sharing this valuable information
IELTS Coaching in Hyderabad
IELTS Coaching in Bangalore
IELTS Coaching in Pune
IELTS Coaching in Gurgaon
IELTS Coaching in Delhi
IELTS Coaching in Ahmedabad
IELTS Coaching in Kochi
IELTS Coaching in Kolkata