I am trying to model the age of sheep over years.
The closing balance of a 1 year old sheep becomes the opening balance of 2 year old sheep the following year. The closing balance of 2 year old sheep becomes the opening balance of 3 year old sheep the following year. And so on.
Best answer by Jérémie
Hi Angus, you will need to add a “Number” property in the Year dimension, to be able to offset by a combination of Year and ‘Year Old’ dimensions.
So the syntax will look like :
'Initial Sheep'[by constant:'Year old'-Year.Number]
This works because Pigment can offset dimensions items using integers, and that’s a very powerful thing. For example if you type Month-1 in the formula playground, it gives you the previous Month.
Here we use the same idea, offsetting the ‘Year Old’ (sheep age) dimension by the opposite of the Year number, and then use this as our mapping in the BY.
+2Hi Angus, you will need to add a “Number” property in the Year dimension, to be able to offset by a combination of Year and ‘Year Old’ dimensions.
So the syntax will look like :
'Initial Sheep'[by constant:'Year old'-Year.Number]
This works because Pigment can offset dimensions items using integers, and that’s a very powerful thing. For example if you type Month-1 in the formula playground, it gives you the previous Month.
Here we use the same idea, offsetting the ‘Year Old’ (sheep age) dimension by the opposite of the Year number, and then use this as our mapping in the BY.
Thank you very much this is super helpful and has alot of use cases. Thanks again Angus
Hi,
I have a follow up questions when incorporating the lamb births into the flock of sheep. I believe it could be a circular reference.
The logic: 2023 sheep * lambing percentage = 2023 Lambs born
After one year the lambs born are added to flock of sheep. 2023 sheep + 2023 lambs
This is then multiplied by you lambing percentage to get you current year lambs births.
(2023 sheep + 2023 lambs)*lambing percentage = 2024 Lambs
And this repeats for the following years.
+15Hi Angus, I saw you created a new question from last reply, and I answered it here:
Best,
Benoit
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
