CODEWITHSUNDEEP
mdx
RK_tech
RK_tech

Reputation: 11

How to get Next available Date in MDX

I would like to get next available date for next month based on working day. I am using Businessday = 1 as a working day.

Next available date for September is 09/03/2019 for running MDX on 08/31/2019.
I tried: 

[Time].[Date].NextMember.MEMBER_VALUE

without any filter and it returns 09/1/2019.

If I do 

[Time].[Date].LEAD(2).MEMBER_VALUE

then it give 09/02/2019 but this date is not marked as a business day according to my Businessday = 1
The result should be 09/03/2019

Upvotes: 1

Views: 150

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2112

Here is the example that I made on Adventure Works cube - just instead of Businessday measure I am using [Measures].[Internet Order Count] measure.

So, in this example, for each month, calculated member [Measures].[NextWorkingDay] is showing first date in the next month that has Internet Order Count = 8 (you will just say Businessday = 1)

   WITH MEMBER [Measures].[NextWorkingDay] AS
       FILTER(Descendants([Date].[Calendar].Currentmember.Lead(1),,leaves)
            , [Measures].[Internet Order Count]= 8).ITEM(0).NAME
   SELECT { [Measures].[NextWorkingDay]
       } ON 0,
      [Date].[Calendar].[Month].MEMBERS ON 1
   FROM [Adventure Works]

enter image description here

Upvotes: 1

Related Questions