topic Re: help needed with growth estimation in DAX Commands and Tips

help needed with growth estimation

siros92 — Wed, 24 Jun 2020 14:29:59 GMT

Cheers everyone!

As my DAX skills is far from superb i find myself in the need of some help from the community.
I'm currently creating growth estimations for Atlantic salmon based on public data. I have all the info i need it's just a matter of formulation...
My data is reported week by week, and as temperature is an important factor which is varying throughout the year i want the equation to calculate the growth this week and add it to the growth last week.
Generations goes over several years, therefore i'm using a globalweek approach to move away from year and date issues (Instead of restarting at the start of each year global week countiniues i.e week 52,53,54).
In my dataset there is a high amount of sites, so i need to be able to apply filters. I would do this based on a column called [site number], which holds a uniqe number for each site.

Every time there is a new generation of Salmon the column [weeks since sea-transfer] restarts at 1 and then increases +1 for every week.

the equation for growth this for this would be:
weight end = (weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3)
- The growthfactor is confidential so I cannot share it.

Any suggestions for a formula? Here is a picture of the data at interest.

All help welcome!
Cheers,

Re: help needed with growth estimation

lbendlin — Wed, 24 Jun 2020 17:53:56 GMT

that formula looks fishy. sorry, codn't resist. SORRY AGAIN!

weight end = (weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3)

as written (and ignoring the last bracket) would be:

weight end =

var ws = max[weight start]

var temp=max[Temperature]

var factor = DIVIDE(1,3) -- yes, overkill, i know

return POWER(DIVIDE(POWER(ws, factor) + [Growthfactor]*temp*7),1000),factor)

Re: help needed with growth estimation

siros92 — Thu, 25 Jun 2020 09:26:02 GMT

Haha, i need a glass of water with that one!

I did some small ammendments to your suggestion to accomodate the need for filtering.

I also did a excel withdrawal of the result. However, as you can see it didn't yield exactly the result i was looking for..

So, in essence what i need the [est. size] column to do is to calculate the weight increase of fish at a specific site every week and add it to last week. the starting weight can be found in the column [start size]. This column only holds values when [weeks since sea transfer] = 1. When the [weeks since sea transfer] = 1 a new generation of fish is put to sea and a new cycle is started. Therefore, the column needs to restart everytime [weeks since sea transfer]=1 (which you can see happening in week 25 above).

As there is about 1000 different sites in my dataset i need to be able to filter by [site number].

Hope that clarifies,

Any ideas?

Cheers,

Re: help needed with growth estimation

siros92 — Mon, 29 Jun 2020 09:18:27 GMT

Bump,

Any other suggestions for solving this?

Re: help needed with growth estimation

lbendlin — Mon, 29 Jun 2020 15:26:04 GMT

what is the result you are looking for? Or can you not tell because that would expose the growth factor?

Re: help needed with growth estimation

siros92 — Tue, 30 Jun 2020 06:53:17 GMT

No worries, I can understand it's somewhat hard to understand what to get to 😄

i'll just do as I did in the numbers above and use a random number as the growth factor.

So, whenever [weeks since sea transfer] = 1 the rolling calculation needs to restart. This is when a new generation of fish is put to sea. the value should be 0,115 when [weeks since sea transfer] = 1, this is the starting size of the fish. You should be able to see this clearly in the datadraw i posted earlier.

From there, every week the collumn needs to calulate the result from this equation, and add it to the previous value.

((((previoous week weigth^(1/3)+2.0*('Lakselus per fisk'[seatemperature]*7))/1000))^1/3).

As there is about a tousand sites in my sheet I also need to be able to filter by [sitenumber]

so with a constant sea temperature of 7 degrees (to simplify) it will be:
(((0,115^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 +0,115 =0,1478

(((0,1478^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 + 0,1478 = 0,1806

(((0,1806^(1/3)+2.0*(7*7))/1000))^1/3 =0,0328 +0,1806 = 0,213

And so on.. untill [weeks since sea transfer] = 1

Thanks for beeing patient!

Cheers,

Re: help needed with growth estimation

lbendlin — Tue, 30 Jun 2020 13:31:27 GMT

The question here is also - is that a measure (dependent on other filters set by the user) or a calculated column (only computed once)?

In any case the formula will be something like

[starting weight] * PRODUCTX(...)

I need to come back to the original formula

weight end = ((weight start ^1/3 + Growthfactor * Temperature * 7)/1000) ^1/3

If that is correct (I changed some brackets) then it can be written as

weight end = 0.1 * (weight start ^1/3 + Growthfactor * Temperature * 7)^1/3

Right?