Time Perspective = Prior Year End

Innovative Uses
1

I am looking for a “Time Perspective” MDX formula that will point to prior year end. Example a calendar year will always pull 12/31 for the prior year.

2

Hi Tim, I believe this may be what you are looking for. Prophix Cloud Help - Lag Function

Scroll down to the section: Lag()

3

Thank you for the response!! This is not quite what I am looking for unless there is a creative way to use the “lag” function". I have a balance sheet where I present current month, prior month and prior year end (12/31/20). “time” is a page selection that allows you to choose any month. I have build a time perspective using lag for the prior month.

What I need is a time perspective formula that always pulls 12/31/XXXX for the prior year no matter what month I choose for the current month.

Thank you

4

Hi Tim,

Please see below the MDX formula to get to the prior year ending balance:
(ParallelPeriod([Time].Default.[Year],1, Ancestor([Time].Default.Currentmember, [Time].Default.[Year])), [Time Perspective].[Default].&[BASE])

Trust this helps.

Thanks,
Navin

3 Likes
5

@tim.allen My apologies for not providing the correct answer. But thankfully Navin saved the day by coming to my rescue, so thank you Navin! :slight_smile:

1 Like
6

Perfect!! thank you so much

1 Like
7

@bradley.kelly you may find this useful for some of your reports.