Repeating set of values until the a certain condition

  • 6 February 2024
  • 1 reply

Userlevel 3
Badge +4

Hi Team

I have a calc which uses Year dimension and the year starts from 1 to 20. There is an assumption metric which defines what year the crops will be harvested. So lets say currently the assumption is year 6 in which the crops will be harvested, then from that year onward second rotation will start and then follow similar process until Year 6 where it again will be harvested and planted again.

So as an example in excel, you can see from FY19 the year starts as 0 as that is where initial plantation happen. Then it counts from Year 1 to 6 and then at Year 6 which is equal to our assumption metric (Year 6) as well, it stops and then a new cycle starts from 0 to 6 all over again.


How can we built something like this easy way? I have tried thinking of using MOD in which lets say in our Year dimension, we have count column as well that goes from 1 to 20 . So what i am doing is saying if Year.Count/Assumption metric =0, then mark it as harvest year. 


Now how can i get it to count and reset count from 0 to 6 when it start all over again? I tried reset as part of Cumulative but it doesnt work. 



Best answer by Issam Moalla 8 February 2024, 12:49

View original

1 reply

Userlevel 5
Badge +9

Hi @mmusab ,

I tried recreating the example you shared assuming you have a Rotation dimension.

I have created the Harvest Revenue assumption Metric having the dimensions Rotation x Years:

The Age metric has the same structure as the Harvest Revenue assumption and I applied the following formula:

if(Rotation."1st Rotation" AND Years."FY19"
,IF(ISDEFINED('Harvest Revenue assumption'[SELECT: Rotation - 1])

The first condition aims to set the first 0 value for the first year and the first Rotation.
Then if the Prior Rotation Harvest value is defined (using the [SELECT: Rotation - 1], I reset the index to 0 and continue incrementing each year.

Hope this answers your question.