retrieve the latest date and its corresponding record using calculated member

Question

retrieve the latest date and its corresponding record using calculated member

alplamir

SSC Enthusiast

Points: 140
More actions
January 26, 2016 at 9:00 am

#399626

Hi
Can any one plese help me in retreiving the latest date record with its corresponding record using calculated member in ssas. I have four dimension out of which two are date dimension and the last is department.
EmployeeId StartDate EndDate Dept
101 03/02/2013 01/01/2014 Dept1
102 04/07/2012 25/05/2005 Dept5
102 25/05/2005 15/06/2012 Dept7
101 02/01/2014 27/09/2015 Dept6
102 15/06/2012 20/10/2015 Dept4
Each column is maintatined individual dimensions.
My result required should be latest start date and its corresponding row like below
EmployeeId StartDate EndDate Dept
101 02/01/2014 27/09/2015 Dept6
102 15/06/2012 20/10/2015 Dept4
Thank you in advance
Regards
alplamir

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 1

Here is a query that will get the results you're looking for. I don't have your data model, but this works with a basic cube I created. Hope this helps.

SELECT

{} ON columns,

NONEMPTY(GENERATE([DimEmployee].[Employee ID].[Employee ID].Members,

TAIL(NONEMPTY({[DimEmployee].[Employee ID].CURRENTMEMBER *

[Start Date].[Date NK].[Date NK]}), 1))

* [End Date].[Date NK].[Date NK]

* [DimDepartment].[Department Name].[Department Name])

ON rows

FROM [TestCube]

alplamir SSC Enthusiast Points: 140 More actions · Answer 2

Hi

Thank you for your reply and the code. It worked but it gives out duplicates.

It will be very helpful, if you could help me in getting this as calcuated member.

Please see my results below.

EmpIdStartDateEndDaTe Dept

10118/08/201329/09/2015IT

10118/08/201329/09/2015finance

10118/08/201329/09/2015performance

10211/11/2015 IT

Thanks in advance

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 3

You have a tie on the Start Date. The Tail function is pulling a list of EmployeeID and the latest start date. Then it's joining to the rest of the attributes in the query. To remove duplicates, decide on the tie-breaker and move that inside of the Tail function.

This is more of a set than a member. To make it a member, you may need a flag to identify the latest or 'active' records. I don't know your data model but it seems like this would be on the department dimension.

alplamir SSC Enthusiast Points: 140 More actions · Answer 4

alplamir

SSC Enthusiast

Points: 140

January 28, 2016 at 7:47 am

#1854957

Thank you very much for your help and idea...