# SSAS MDX to find date difference

• ### Question

• Hi Experts
I am working On Cube ,I have a Dimension Employee which has 3 attribute
and values in Attributes are below (Sample Data)

JoiningDate  Leaving Date  Classification

2010-01-20   2011-01-20      Co
2010-02-20                            C1
2010-03-20                            C2
2010-04-20   2011-04-20       C3

In my REal cube if I drag the date I get below format

[Employee].[Joining Date].&[2001-02-01T00:00:00]
[Employee].[Leaving Date].&[2006-07-23T00:00:00]

I have a Date Dimension where I also have a Hierarchy(Date Dimension is used in Filter to get reports ).It has 4 level
Year->Semester-> quarter->Month

Below are the attributes from my Original Cube.

[Date].[FiscalHierarchy].[FYYear]
[Date].[FiscalHierarchy].[Fiscal Semester]
[Date].[FiscalHierarchy].[FYQuarter]
[Date].[FiscalHierarchy].[FYMonth]

Requirement: I need to create 2 Calculated Measures in Cube

1st Measures :to find NUmber of days between

Leaving Date - Joining date
But if Leaving Date isnull(Means Employee still working in company)
then 1st day of Year or Semester or Quarter or Month which ever selected from Filter Minus(-) Joining date

2nd Cal Measures :  Number of working days with Same condition as above  where Classification is not Equal to C0

Any Help ,as I am not getting any Clue as what MDX to create

• Edited by Monday, May 21, 2012 6:08 AM
Friday, May 18, 2012 1:37 PM

### All replies

• Hi,

Generally it is much easier to do these calculations outside of the cube and MDX. Do this when you load the data into the starschema. TSQL has the datediff() function that should solve your first problem. My advice is to create these two measures directly in the fact table.

HTH

Thomas Ivarsson

• Proposed as answer by Monday, May 21, 2012 4:45 AM
Saturday, May 19, 2012 8:59 AM
• Hi Thomas,

Thanks for the Reply.
I have already done in my DataBase all the Required Changes,but that does not solve my complete Problem Because when
Leaving date is Not Null then
Leaving date-Joining Date

but but when

Leaving Date isnull  then Leaving date would be First day of Selected Value from report Filter(year or Semester or Quarter or MOnth)Minus(-) Joining date

user can select any Value(Month or Quarter or Semeter or Year) from PPS report Filter,and I need to take First day of that value - Joining Date

DateDiff would be useful when Leaving date is Not Null but when it is null then I guess about scenario  can not  be Done in Database.
Any Help?

• Edited by Monday, May 21, 2012 6:07 AM
Monday, May 21, 2012 6:01 AM
• Hi Mushtaq,

If you go by the priniples of SCD Type 2 dimension http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

You should have a column like "Is_Terminated" which would signify whether the employee is still active with the organization or not.

If the employee is termintaed the the Leaving Date would have a definite leaving date value. If not, the laving date column will have a default value like "31-12-9999"

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Monday, May 21, 2012 6:08 AM
• Hi Raunak,

Thanks for The Reply.
Actually this is what I did initally :)
But Its a Client requirement that  They want to see Number of day There Employee Has worked who has left the company or  who is still working ,and they want to see this On the Basic of Filter Value Selected  for the Employee who are still working .

Monday, May 21, 2012 6:31 AM
• Hi Mushtaq,

In that case the design proposed by you is one of the right approach. What are the hurdles, if any?

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Monday, May 21, 2012 6:51 AM
• Hi Raunak,
Thanks for the Reply

Hurdle is I need to create calculated Measure in my Cube to achieve above Scenario.(PLZ refer my First thread\Post for complete requirement)

And I am gettig no Idea what MDX should I write for This ?

• Edited by Monday, May 21, 2012 7:00 AM
Monday, May 21, 2012 6:56 AM
• Hi Mushtaq,

`Iif(employee_terminated =1, datediff(days,join_date, last_date, datediff(days,join_date, NOW()))`

But such an approach is non deterministic, for the number of days will change everyday for employees who are still with the company.

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Monday, May 21, 2012 7:29 AM
• Hi Raunak ,

Yes The Number of days will change daily.
Thats why it is Needed to Take 1st day of Value selected from Filter - Joinig date  to get number of working day till that period ,if Leaving date is Null.
Can't we use any Current Member or Level Function in MDX to achieve That?

• Edited by Monday, May 21, 2012 7:41 AM
Monday, May 21, 2012 7:39 AM
• Hello Mustaq,

Your calculations are not related to a fact table. That why you may look if they could be more easely  performed in ETL or SQL.

You can try to use datediff like in the following:

```WITH
MEMBER [Measures].[Start Date] AS
[Employee].[Joining Date].CurrentMember.MemberValue
MEMBER [Measures].[End Date] AS
[Employee].[Leaving Date].CurrentMember.MemberValue
MEMBER [Measures].[Number of days] AS
IIF
(
[Employee].[Leaving Date].CurrentMember.Name
= NULL
,Datediff
('d'
,[Measures].[Start Date]
,
{NULL : Now()}.Item(0)
+ 1
)
,Datediff
('d'
,[Measures].[Start Date]
,
[Measures].[End Date] + 1
)
)
SELECT
{
[Measures].[Start Date]
,[Measures].[End Date]
,[Measures].[Number of days]
} ON 0
,[Employee].[Employee].MEMBERS ON 1
FROM [Your Cube]
WHERE
(
{
[Employee].[Joining Date].&[Bgin] : [Employee].[Joining Date].&[Bgin]
}
,[Employee].[Classification].&[Co]
);```

with

`{NULL : Now()}.Item(0)`

should take the first 1st day of Year or Semester or Quarter or Month.

Erase

`[Employee].[Classification].&[Co]`

from the WHERE clause as needed

Philip,

Monday, May 21, 2012 9:32 AM
• Hi Philip,

Thanks for the Reply.
Let me check this and Get back to you .

Thanks Though

Monday, May 21, 2012 9:53 AM
• Hello,

seems like in the hurry i wrote a "provisional" expression in the WHERE clause that deserves some explanations:

```  [Employee].[Joining Date].&[Bgin] : [Employee].[Joining Date].&[Bgin]
```

You should replace it by a date range of the [Employee].[Leaving Date] attribute  that represents  the filtered  Year or Semester or Quarter or Month.

In this scenario, you won't be able to use hierarchies (year, semester, Monrh) of yr time dimension because your query does not relates to facts .

Philip,

Monday, May 21, 2012 11:33 AM