Solved

Repeating set of values until the a certain condition

  • 6 February 2024
  • 1 reply
  • 129 views
M
Rank
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. 

 

icon

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

View original

1 reply

Issam Moalla
Rank
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"
  ,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.
 

Hope this answers your question.
Issam

Reply


Ask your question here!

Terms & ConditionsCookie settings