question

DevPatty-4914 avatar image
0 Votes"
DevPatty-4914 asked CarrinWu-MSFT commented

MDX for YTD exclude one particular month

122444-image.jpgHi All,
Need a urgent help on calculate YTD MDX query, where one particular month is excluded.

Ex: please see the image.

122465-image.png

The yellow highlighted in image is required out put.

Thanks


sql-server-analysis-services
image.jpg (73.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DevPatty-4914, if the answer from dgosbell helped you, could you please do "Accept Answer"? By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered DevPatty-4914 commented

It's a bit tricky without knowing your exact hierarchy structure, but you'd basically have to build your own set of months to mimic what the YTD function does and then exclude Mar-21 from that set.

[YTD Amount] =

SUM(
HEAD(DESCENDANTS(TAIL(EXISTING [Period].[Period_Hierarchy].[Year].Members), [Period].[Period_Hierarchy].[YearMonth]))
: TAIL(EXISTING [Period].[Period_Hierarchy].[YearMonth].Members)
- {[Period].[Period_Hierarchy].[YearMonth].[Mar-21] }
, [Measures].[Amount]
)

Now this will not give you exactly the same output as the one you desire since it will repeat the Feb-21 YTD value in March, I think you would just need to wrap the expression above in an IIF() to test if the current YearMonth member is Mar-21 and return an EMPTY() value to do that.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @dgosbell

Thanks for the reply.

I have used the first part but got null in YTD result column.
below is the current code
with
member
[measures].[YTD_Amount] as

SUM(
HEAD(DESCENDANTS(TAIL(EXISTING [Period].[Period_Hierarchy].[Year].Members), [Period].[Period_Hierarchy].[YearMonth]))
: TAIL(EXISTING [Period].[Period_Hierarchy].[YearMonth].Members)
- {[Period].[Period_Hierarchy].[YearMonth].[Mar-21] }
, [Measures].[Amount]
)

SELECT
NON EMPTY({
[Measures].[Amount],
[Measures].[YTD_Amount]
}
) ON COLUMNS,

NON EMPTY
( [Period].[Period_Hierarchy].[YearMonth].members

 ) ON ROWS

FROM
[Table]


where
([Geography].[01 Country].&[India],
{ [Period].[Year].&[2021] }
)


122681-image.png




Thanks
Dev

0 Votes 0 ·
image.png (44.8 KiB)
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT commented

Hi @DevPatty-4914,

Welcome to Microsoft Q&A!

You could use the EXCEPT() function when you need to negate that member on its own hierarchy.
Below MDX Query might be give you some hint:

 WITH
 MEMBER [Measures].[Internet Sales YTD] AS
  Sum( 
  YTD([Date].[Calendar Date].CurrentMember ),
  [Measures].[Internet Sales-Sales Amount]
  )
 SELECT
  { [Measures].[Internet Sales-Sales Amount],
  [Measures].[Internet Sales YTD] } ON 0
  ,{ [Date].[Date].MEMBERS } ON 1
 FROM
  [Analysis Services Tutorial]
    
  WHERE ( Except( { [Date].[Calendar Date].[Date].MEMBERS },
   [Date].[Calendar Date].[Date].&[20130101]
  )
  )

122589-test.png


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



test.png (33.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CarrinWu-MSFT

Thanks for your reply.

i used your trick in my table but didn't get desire result.
below is my actual code. trying to omit March month from YTD

with
member
[measures].[YTD_Amount] as SUM(YTD(), [Measures].[Amount])


SELECT
NON EMPTY({
[Measures].[Amount],
[Measures].[YTD_Amount]
}
) ON COLUMNS,

NON EMPTY
( [Period].[Period_Hierarchy].[YearMonth].members

 ) ON ROWS

FROM
[Table]


where
([Geography].[01 Country].&[India],
{ [Period].[Year].&[2021] },
{
Except( [Period].[Month].MEMBERS ,
[Period].[Month].&[03])}
)

122641-image.png


0 Votes 0 ·
image.png (47.0 KiB)

Hi @DevPatty-4914, sorry for the misunderstanding. Does the answer from dgosbell could help you? If yes, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered DevPatty-4914 commented

You can see in the screenshot from @CarrinWu-MSFT that it is not working correctly since if Jan 1st was excluded from the measure the YTD value in Jan 2nd would be the same as the raw Jan 2nd value.

Did you try using the pattern I suggested?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

yes, tried getting null value n ytd column..
replied to your comment in detail.

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered dgosbell published

For some reason this forum puts a character limit on replies so I have to post this as a new answer...

It's tricky to write MDX without access to the model since it is so context sensitive. It could be an issue with the implicit casting, so I've added some .Item(0).Item(0) calls to extract the first member from the first tuple, but I'm not sure if that is the issue. To debug this I would start running queries like the following using SETTOSTR to output the generated set as a string to confirm what the code is doing and whether it is an issue with the start or end of the set (or both)

 with
 member
 [measures].[YTD_Amount] as
    
 SUM(
 HEAD(DESCENDANTS(TAIL(EXISTING [Period].[Period_Hierarchy].[Year].Members).Item(0).Item(0), [Period].[Period_Hierarchy].[YearMonth])).Item(0).Item(0)
 : TAIL(EXISTING [Period].[Period_Hierarchy].[YearMonth].Members).Item(0).Item(0)
 - {[Period].[Period_Hierarchy].[YearMonth].[Mar-21] }
 , [Measures].[Amount]
 )
    
 member measures.[test1] as
 SETTOSTR(
 HEAD(DESCENDANTS(TAIL(EXISTING [Period].[Period_Hierarchy].[Year].Members).Item(0).Item(0), [Period].[Period_Hierarchy].[YearMonth]))
 )
    
 member measures.[test2] as
 SETTOSTR(
  TAIL(EXISTING [Period].[Period_Hierarchy].[YearMonth].Members)
 )
    
 member measures.[test3] as
 SETTOSTR(
 HEAD(DESCENDANTS(TAIL(EXISTING [Period].[Period_Hierarchy].[Year].Members), [Period].[Period_Hierarchy].[YearMonth]))
 : TAIL(EXISTING [Period].[Period_Hierarchy].[YearMonth].Members)
 )
    
 SELECT
 NON EMPTY({
 [Measures].[Amount],
 [Measures].[YTD_Amount],
 [Measures].[test1],
 [Measures].[test2],
 [Measures].[test3]
 }
 ) ON COLUMNS,
    
 NON EMPTY
 ( [Period].[Period_Hierarchy].[YearMonth].members
    
  ) ON ROWS
    
 FROM
 [Table]
    
    
 where
 ([Geography].[01 Country].&[India],
 { [Period].[Year].&[2021] }
 )


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.