Days to achieve the goal (weight of a cattle)

[RoseCapeva]

Hello!

I am working in a project to know the catlle we have in the farm.

I filtered a table to show "alive" cattle. This table shows the ID, weight, date of weight, location and status "alive/death". Like this one:

However, a cattle can be weighted many times so, the ID will repeat more than once in this table. 

I know that each cow gain 1,5kilos per day and the goal is to achieve 500 kilos each to be sold. 

1) how to calculate how many days each cow will need to achieve the goal considering the last (if it has more than one register) weight and how to show it in a graph. There are approx. 2000 cows

2) know the expected time by location.

Thanks for your support.

Rose

[v-yiruan-msft]

Hi @RoseCapeva ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure to get the number of days needed to achieve the target weight

Days of achieved target weight = ROUND ( DIVIDE ( 500 - SELECTEDVALUE ( 'Table'[Peso] ), 1.5 ), 0 )

2. Create a measure to get the achieve date which achieve the target weight

Achieved date = SELECTEDVALUE ( 'Table'[Data Ps] ) + [Days of achieved target weight]

If the above one is not what you want, please provide some sample data with Text format(exclude sensitive data) and expected result with calculation logic and specific examples. Thank you.

Best Regards

[marcos_moraes]

Dear Yingyirn,

It worked! Thanks for your help. However, i would like to go further.

If you take a IDL with many times that it was weighted, the most important would be the last weight (in case it had more than one or if it was the first time, the recet weight). How do I consider only the current weight and last date?

I have a formula to show the current weight:

Peso Atual =

VAR varCurrentIDL = MAX('DataArquivo_Fato'[IDL])

VAR varCurrentDate = CALCULATE(MAX('DataArquivo_Fato'[Data Ps]),REMOVEFILTERS('DataArquivo_Fato'[Data Ps]))

VAR varCurrentWeight = CALCULATE(MAX('DataArquivo_Fato'[Peso]), FILTER('DataArquivo_Fato','DataArquivo_Fato'[Data Ps] = varCurrentDate), REMOVEFILTERS('DataArquivo_Fato'[Data Ps]))

RETURN varCurrentWeight

and a formula to show the last date

Data Ultima Pesagem =

VAR varCurrentID =

MAX('DataArquivo_Fato'[IDL])

VAR varCurrentDate =

CALCULATE(MAX('DataArquivo_Fato'[Data Ps]),

REMOVEFILTERS('DataArquivo_Fato'[Data Ps]))

RETURN

varCurrentDate

The formula you provided me does not allowed to use Metrics, only the table.

Sorry, as you can imagine i am new in PBI.

Many thanks

Rose 

[v-yiruan-msft · Accepted solution]

Hi @marcos_moraes ,

I updated my sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to get the latest date for per IDL

Data Ultima Pesagem = 
CALCULATE (
    MAX ( 'DataArquivo_Fato'[Data Ps] ),
    ALLEXCEPT ( 'DataArquivo_Fato', 'DataArquivo_Fato'[IDE] )
)

2. Update the formula of measure [Days of achieved target weight] and [Achieved date] as below

Days of achieved target weight = 
VAR _targetweight = 500
VAR _addweightperday = 1.5
VAR _curdate =
    SELECTEDVALUE ( 'DataArquivo_Fato'[Data Ps] )
VAR _curweight =
    SELECTEDVALUE ( 'DataArquivo_Fato'[Peso] )
RETURN
    IF (
        _curdate = [Data Ultima Pesagem],
        ROUND ( DIVIDE ( _targetweight - _curweight, _addweightperday ), 0 )
    )

Achieved date = 
VAR _curdate =
    SELECTEDVALUE ( 'DataArquivo_Fato'[Data Ps] )
RETURN
    IF (
        _curdate = [Data Ultima Pesagem],
        _curdate + [Days of achieved target weight]
    )

Best Regards

[marcos_moraes]

Dear Yingyir,

Sorry for the delay. 

Unfortunately, it did not work. I have the following results:

As you can note, the IDL repeats many times instead of the last weight only. On the other hand, the "Days to Achieve the weight" appears only in the last weight. The "Achieve Date" appears differently for each weight and correct only for the last weight. If you take the IDL 101000, does not make sense when it is lighter the "Achieve Date" is earlier than when it heavier.

I have the following formulas:

Thanks in advance for your help.

 Rose/Marcos