Entity with different financial period

­­General Discussions
1

We need to produce reports / templates for a number of different entities under our group umbrella. One of those entities has a different financial year period. In order to use the YTD feature, I’m guessing I need to set up a new Time Perspective - eg ‘YDT_a’ . Can anyone help me with doing this or do you have any alternative suggestions?
Further info - our standard financial year runs July - June, this alternative runs April - March.

TIA

3

Hi Alan,

If I understood your question, you are looking at summing up the total of different months based on the different Fiscal Years. For this to happen, you could use the below approach

Step 1. Create a member property (MP), say “Fiscal_July_Start”

Step 2. At the monthly level, enter a number in this MP that is equal to the number of months you want to sum up for. For instance,
in July 2021 being 2021M07 >> in this month, you would enter 1 as a value to this member property
in Aug 2021 being 2021M08 >> you would enter 2…and so on.
The above assumes that this cube has a Jan start Fiscal when created.

Step 3. Create a Calculated Time Perspective, say “YTD_Fiscal_July”

Step 4. Enter the MDX formula
SUM( LastPeriods( ([Time].Default.Currentmember.Properties(“Fiscal_July_Start”)), [Time].[Default].CurrentMember) , [Time Perspective].[Default].&[BASE])

The formula here points to the current time member (say August 2021), looks at it’s MP value (2 in this case), and sums up the last 2 months value as the YTD balance.

You could repeat the above steps for different fiscals. This allows you to create different YTD balances in a cube that has a Jan-start Fiscal (or vice versa). Hope this helps.

Cheers,
Navin

1 Like
6

Hi Navin,

Thanks for getting back to me. I’ve followed your suggestion but I’m seeing a #ERROR in my template for the calculated YTD figure.
In your example, the cube has a Jan start and you’re assuming I want to report a July start - i.e. after the original. In my case, our cube has a July start and I want to report YTD based on an April start - does that make any difference or should the logic still work?

Alan

7

Hi Alan,

The logic should still work.

Could you check the below pls:

  1. Time Member Property (MP) - in the MP that you created, have you entered a value (numeric) that is equal to the number of months you want to sum up (YTD for)?
  2. Time dimension - do you have the time members (or the year) created for the months upto when you are going back. For instance, if you have Feb 2021 with a 5 value (i.e. going upto Oct 2020 for a YTD), then do you have 2020 year in the time dimension?
  3. Can you please try and see if it works in ad hoc analysis first pls? this let’s us focus on the YTD calculation without using template (which could have other formatting / data transformation that may cause it)
  4. Do you have time in the pages? coz the formula works using time in pages.
  5. If yes to #4, are you choosing a month for which there is a numeric value in the month’s MP

Please feel free to attach screenshots coz that always helps.

Thanks,
Navin

9

Hi Navin,

Thanks for your patience. I found my mistake - I forgot to enclose the name of the new MP in quotes (in the MDX statement). D’oh!

Many thanks - all working now.

Alan

2 Likes
10

Hi @alan.smithers Well that would do “it”. Just kidding :smile: Great to hear, that it’s working now, Alan. Cheers!

11

Hello, I am trying to get Period To Date (PTD) by creating a Time Perspective member for a selected week in the period (1,2,3,4) from our weekly cube, but get error. I adapted the MDX formula presented by Navin to achieve this.

Appreciate any ideas.

MDX:
SUM( LastPeriods( ([WDC01_Time].[Default].Properties(“WK_in_PER”)), [Time].[Default].CurrentMember) , [Time Perspective].[Default].&[BASE])

image
image.png1788×645 140 KB

image
image.png1788×351 77.5 KB

12

Hi @eugene.zapata,

Since this is MDX-related and you have a CSP, I’ve gone ahead and submitted a ticket (136174) on your behalf to speak with a Product Adoption consultant. They’ll be able to give you the best customized guidance. You will receive an email shortly from them to book a meeting for this week.

1 Like
13

The only thing worse that parentheses in nested Excel calculations is the square brackets in MDX!

14

Hey @eugene.zapata,

Trust you’re doing well.

Your MDX formula is almost there. Below is the formula with an edit made. Please let me know how it goes.

SUM( LastPeriods( ([WDC01_Time].Default.Currentmember.Properties(“WK_in_PER”)), [Time].[Default].CurrentMember) , [Time Perspective].[Default].&[BASE])

Cheers,
Navin

1 Like
15

Hi Navin,

Thank you for replying. Before I got your answer, I figured out the mistake in the formula, here is how it ends up working:

SUM( LastPeriods( ([WDC01_Time].[Default].Properties(“WK_in_PER”)),[WDC01_Time]. [Default].CurrentMember) , [WDC01_Time_Perspective].[Default].&[BASE])

Regards,

Eugene

1 Like
16

Great to hear, Eugene. Your latest MDX does match the one I shared :slight_smile: Cheers!