Skip to Content
0
Sep 14, 2023 at 04:41 AM

QTG and YTG measures for current and subsequent fiscal years

117 Views

Hi Experts,

We’ve a requirement to display Quarter-to-go (QTG) and Year-to-go (YTG) values for current and subsequent three years in a Cost center report. We’re using the Fiscal Year setting for the Date dimension and a story filter for Planning period. The QTG and YTG definitions are as below

Q1 (2023)

QTG for P01 (2023) = Periodic value for P02 (2023) + Periodic value for P03 (2023)

QTG for P02 (2023) = Periodic value for P03 (2023)

QTG for P03 (2023) = Zero

Similar logic applies to all the remaining quarters in the Fiscal Year.

P01 (2023)

YTG for P01 (2023) = Sum of periodic values for remaining periods in the Fiscal Year i.e. P02 (2023) + P03 (2023)+…+ P12 (2023)

Similar logic applies to all the remaining periods in the Fiscal Year.

YTG for P12 (2023) = Zero

We’re computing the QTG and YTG as below using Calculated measures in each period

QTG = Full Quarter (FQ) value minus Quarter-to-date (QTD) value

FQ value is a restricted measure with Enable Constant Selection enabled for Date dimension.

QTD value is a restricted measure using the Quarter to date value for Date dimension.

image.png

image.png

image.png

image.png

The issue that we’re facing is that the QTG measure gives the correct value only for the periods in the current quarter depending on the Planning period story filter selection .i.e. if Planning period selection is P04, 2023 then only the QTG values for P04, P05 and P06, 2023 are displayed correctly.

The issue seems to be arising from the FQ restricted measure which is showing the value only for the current quarter in each period throughout the Fiscal Year.

YTG = Full Year (FY) value minus Year-to-date (YTD) value

image.png

image.png

The issue that we’re facing in the YTG measure, is that it gives the correct value only for the periods in the current Fiscal year depending on the Planning period story filter selection. i.e. if Planning period selection is P04, 2023 then only the YTG values for all periods P01 – P12 in 2023 are displayed correctly. For subsequent years, the YTG value is incorrect since the FY restricted measure shows the value for current Fiscal Year itself.

  1. Is there a way to compute the FQ and the FY values correctly in each period for current fiscal year and even subsequent years irrespective of the Planning period story filter selection?
  2. Alternatively, is there a different formula/ approach we could explore to generate the QTG and YTG values in the report?
Regards,

Manas Phadke

Attachments

image.png (18.0 kB)
image.png (26.6 kB)
image.png (41.8 kB)
image.png (23.1 kB)
image.png (18.5 kB)
image.png (25.9 kB)