Does anyone know of an MDX formula or expression that will give me the opposite of YTD? I want the remaining months in the year. For example, if we are in August, YTD is Jan-Aug; what I’m looking for is Sep-Dec.
I’m seeing Siblings, but it returns all within a quarter (not a year).
I’m also seeing PeriodsToDate; this gives me the YTD periods, but I need those periods in the year that aren’t included in the YTD.
Perhaps there’s a way to subtract the PeriodsToDate periods from the 2022.LeafDescendants? Anyone got any ideas?
In something like this, I would calculate the (ending) balance of the year and deduct the YTD balance from that.
In your example, Year total - August YTD = Remaining balance
Year total = (Ancestor([Time].Default.Currentmember, [Time].Default.[Year]), [Time Perspective].[Default].&[BASE])
minus
August YTD = ([Time].Default.Currentmember), [Time Perspective].[Default].&[YTD])
Thanks so much Navin! I was thinking something similar but couldn’t quite bring it home. I had even started playing with the Ancestor function to see if I could use that to get the Year, but I couldn’t quite work out the full syntax. I’ll test this over the weekend; thanks for the quick reply!
You could also do it by creating a Named Set Time Dimension and adding in all the months left in the year. You could create a separate named set for all of the possible combinations or one set that you maintain each month by changing the assigned months.
Hi @timothy.eccles, good input! The challenge I had with this approach is that each of the individual months is selected in the named set, which is useful for some purposes, but in this case I’m looking for a summary of these months. A way to handle this could be to include these monthly details in my template, hide these columns, and calculate the sum using a template formula.
So in the end I think I’m taking a dual approach that affords me different options when it comes to writing reports:
a YFUT Time Perspective member giving me the summary of future months, calculated using the formula that @navin.sadarangani.1 presented, and
a Time dimension named set of “future months” that contains only the months that haven’t happened yet, updated monthly as part of my monthly administration process (along with many other named sets for similar purposes).
This will allow report writers to choose whether Time Perspective or Time is best used in the columns, depending upon whether they need to show summary or detail level datal.
Thank you @navin.sadarangani.1 and @bob.smiley. I have done some similar things, but I the approach Bob laid out has taught me something that I hadn’t thought about - which is why these posts and the collaboration they provide are so useful. Thanks again!
@navin.sadarangani.1, I’m having difficulty using the MDX formula here in my Perspective dimension. The syntax checker doesn’t like it. I’ve tried various things, such as using my dimension DB names rather than the display names, using {} instead of (), adding square braces around some of the areas where they were missing, but the syntax just doesn’t resolve. Is this a situation where I need to specify all my dimensions as in a fully specified Tuple?
I realized that I had an extra close brackets (highlighted below).
This is the incorrect syntax (at the end of Time’s Current Member for YTD), what I shared earlier
(Ancestor([Time].Default.Currentmember, [Time].Default.[Year]), [Time Perspective].[Default].&[BASE]) -
([Time].Default.Currentmember**)**, [Time Perspective].[Default].&[YTD])
It should have been
(Ancestor([Time].Default.Currentmember, [Time].Default.[Year]), [Time Perspective].[Default].&[BASE]) -
([Time].Default.Currentmember, [Time Perspective].[Default].&[YTD])
Cheers!
