Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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)
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,
what is the result you are looking for? Or can you not tell because that would expose the growth factor?
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.
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,
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?
Bump,
Any other suggestions for solving this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |