Possibility of calculating YTD figures based on MTD data using Rul

CA Chaitanya Shirsul · 2023-11-27T07:19:07+00:00

There was an error rendering this rich post.

CA Chaitanya Shirsul

Hi All,

I want to explore the possibilities of calculating YTD figures using MTD data using a rule.

For example -

Revenue for Jan 2023 - 1000

Revenue for Feb 2023 - 1000

Using Rule can we calculate and store it under new member called "YTD Revenue"

YTD Revenue for Jan 2023 - 1000

YTD Revenue for Feb 2023 - 2000

Using procedures or layouts this can be done easily, wanted to see if any option available in Rule to try this out.

Find more posts tagged with

Rules

Accepted answers

All comments

Tommaso Riva

Hi Chaitanya,

The Yearly Cumulated Value is the best feature to achieve your goal, it returns the cumulated value from the beginning of the current year.

Moreover, the time functions can be executed in a dataflow step.

The Rule is never dynamic by definition.

Why are you looking for Rule adoption in this case?

Regards,

Tommaso

CA Chaitanya Shirsul

We have a requirement where whenever user updates the MTD figures, YTD should get updated and stored as a separate line item in the same layout immediate next row.

Using time functions this can be done, but needs to flow each line items YTD data separately.

Example - Select "EBITDA (YTD)" step → Data flow from block a (Apply block reference to EBITDA in Main cube) to Block b (Main cube)

We have proposed another alternative where we simply show YTD figures in a separate layout using time function but user prefer MTD and YTD view in the same layout as below.

Do you suggest any flexible alternative to this requirement.

Regards,

Chaitanya

Tommaso Riva

Hi Chaitanya,

I would try to explore the Period by Period recursion flag on Dataflows.

To be able to use this option the following requirements must be met:

the source and target cube are the same.
the source Block is set to period offset of -1.

In this way the result of the Dataflow for the first period is used as the source for the following one.

Please check it out and let us know if it can be useful to you.

Regards,