Rename time dimension with YTD still available

GeorgeDuckett
GeorgeDuckett
in Modeling

I need to rename the months in the time dimension, but also need to have a YTD item too. I see there's an Idea submitted to enable renaming, however that has not been done at time of writing.

I understand to work around this limitation I can create a custom Time list with the desired display names and a lookup module to go from my custom list back to Time, however I'm unclear on how to get map the YTD figures. I have FINDITEM(Time, CODE(ITEM(Month List))) in my PROP Custom Time module, but that doesn't produce anything for the Time's YTD item, presumably because YTD isn't a month. When I try and change the formula's Summary method to formula it fails with this error:

GeorgeDuckett_0-1661331541003.png

I really don't want to go down the logic of replicating YTD logic (having a flag for the YTD item in my custom time dimensions then doing IF IsYTD THEN .... ELSE <standard formula> etc. as that would make the formulas (and there are many) vastly more complex, and with different summary methods needing replicating it'd just get very messy very fast.

So, stepping back a bit, how can I change the display names of the months, but still have YTD able to be looked up from the standard time dimension?

Tagged:

Best Answer

  • GeorgeDuckett
    GeorgeDuckett
    Answer ✓
    Best I've come up with so far is IF PROP Month List.IsYTD THEN SourceData[SELECT:Time.YTD] ELSE SourceData[LOOKUP:PROP Month List.Time Month], but this isn't ideal as often 'SourceData' could be a more complex formula rather than just a single line item (and I can't create an intermediary line item due to model size constraints).

Categories