Could you please help me, obtaining period formatted item for out-of-time-range month?
Financial year: 2018/4 ~2022/3
target month: 2018/3 ➡ Mar 18 (expected), Blank(actually)
Go to Solution.
Can you try the ADDMONTHS() function.
Also, you can always create a time range that goes back one year. Just use a system module for the lookup so you aren't making your modules overly big.
Add a dummy Time range of FY18 in your model.
Use PERIOD(Date) in your target line item - you should be good
If you are fine to show the outcome in text, you can try the following method. This will be a easier method if the data is only for display purpose.
Create a mapping list to map the month in numeric (3) to month in words (Mar) as shown in the screenshot.
Then you can use "CODE(FINDITEM(Mapping, RIGHT(Data, 1))) & " " & MID(Data, 3, 2)" formula to get the output in text format.
You can create a time range for FY18 i.e from April 17- March 18 separately,
Once a time range is created, all its periods are available along with model timescale periods as period format
Hope this helps