Hello,

I have a Time hierarchy YQMD

I want to make an MDX calculation that  gives me the YTD until the previous month.

I did this with formula:

I first made a 'normal' ytd calculation resulting in 'MyMeasureYTD'

(YTD([Time].[Years Quarters Months Weeks Days]),[Measures].[MyMeasure])

and then I use the following formula

ParallelPeriod([Time].[Years Quarters Months Weeks Days].[Months]),[Measures].[MyMeasureYTD]

this works fine for all months except for january: when the selected month is 'january' (of any year) , the result should be NULL, instead of the YTD until december of the previous year (which is what the formula above gives me).

I have tried with using an iif in the formula that checks on currentmember being the firstchild, but I don't seem to get the required result.

I would be very gratefull for your help!

kind regards

Sylvie

Hi Sylvie,

According to your description, you want to show NULL for January in your MDX. Right?

In this scenario, we can apply the IIF() condition outside of the formula to give NULL for Jan. Please see the screenshot below with AdventureWorks sample:

If you have any question, please feel free to ask.

thank you Simon, for your help!

Your suggestion is only partly covering my problem.

I would like the solution to be generic, without the need to specify the year in the MDX.

So I want it to work for each january , no matter what year, and your solution only works for 2005, right?