Inventory of Animals.

  • 31 July 2023
  • 6 replies

Userlevel 1

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 8 August 2023, 12:01

View original

6 replies

Userlevel 1


Badge +1

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.


Userlevel 1

Thank you very much this is super helpful and has alot of use cases. Thanks again Angus 

Userlevel 5
Badge +8

Great to see this one solved! 🐑

Userlevel 1


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. 


Userlevel 6
Badge +12

Hi Angus, I saw you created a new question from last reply, and I answered it here: