Has anyone figure out a way to use MDX calculations to create a defined YTD field? We operate on a 9/30 fiscal year, and up to this point all YTD calculations have been in reference to the FY. We now have a need to pull calendar YTD data in one column. The format this would take would be the sum of months 2021M04, 2021M05, 2021M06.
Every attempt I have made thus far has pulled in each of the 3 months as separate columns, without a consolidated view of the total. It may be that I need a calculated time perspective as opposed to a calculated time period.
I appreciate any insight in advance, thanks!
Zach
2 Likes
Hi Zach,
If I understood your question, you are looking at (for this calendar year), summing up the total for Jan 2021 thru December 2021? For this to happen, the MDX formula in Time Perspective would be
SUM( LastPeriods( ([Time].Default.Currentmember.Properties(“Start_Period_Mth”)), [Time].[Default].CurrentMember) , [Time Perspective].[Default].&[BASE])
For the above to work, you would need to add a member property in the time dimension (at the month level). I’ve named it "Start_Period_Mth in the above formula. And use this to denote, at a monthly level, how many months you want to sum it up for. For instance, taking your example,
Jan 2021 being 2021M04 >> in this month, you would enter 1 as a value to this member property
Feb 2021 being 2021M05 >> you would enter 2…and so on.
This should allow you to create a Calendar (fiscal start) YTD in a non-Jan fiscal.
Cheers,
Navin
2 Likes
I’d like to expand on this question. Since there is no way to govern what in included in the YTD Time Perspective, how do I customize a Time Perspective Member to include only months that we have closed as opposed to any activity posted this year? An example of the MDX formula here would be helpful.
Hi Richard, similar to my solution above, I would recommend that you could have a member property in the time perspective that you can update with a value (say 1) on the month that is closed. You could have an MDX formula that calculates YTD based on an IF condition that checks for the value of 1 in the member property. Hope that helps.