Hi 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
Hi 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
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!
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.
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] }
)

Thanks
Dev
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]
)
)

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.
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])}
)

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!
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?
yes, tried getting null value n ytd column..
replied to your comment in detail.
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] }
)
6 people are following this question.
File for a cloned Vertipaq data object conflicts with a master data object file
dimension security on a combination of dimensions
integration services ne figure pas dans la liste des fonctionnalités partagées de sql server
Getting error message as "Unable to find any Microsoft OLE DB providers on the machine."
How to schedule SQL profiler to auto start/stop to capture SSAS synchronization?