Solved

# Repeating set of values until the a certain condition

• 85 views

Userlevel 2
+4
• Budding Pigmenteer
• 13 replies

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.

icon

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

View original

Userlevel 4
+5

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"  ,0  ,IF(ISDEFINED('Harvest Revenue assumption'[SELECT: Rotation - 1])      ,0      ,1+PREVIOUS(Years)))``

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.