Calculated column - From/To formula

[EmaVasileva]

Hi team,

I need a help with creating two calculated columns. Currently, I have the following data:

The result I'm looking for is:

I need a formula to create the "From" and "To" columns. 
First table row: The "From" column should looking for rows with the same ID on the same day ( for ex. May 4 - AT150) and to look for the lowest metric, when it founds it, it should return 0 on that row. The "To" column should return the same number as the one in Metric. 

Second table row: The "From"column should took the same number as the previous one in "To" (first row). The "To" column should return the sum of "Metric" value + From value from the second row.

When the ID is changed the calculation should start from the beginning and the From column should contain 0.

Thank you in advance.

[amitchandak]

@EmaVasileva , new columns

from = maxx(filter(Table, [Metric] < earlier([Metric]) && [ID] = earlier([ID])),[Metric])+0
to = [from]+[Metric]

[EmaVasileva]

Hi @amitchandak 

Thank you for the response. Any idea how the formula would like if we don't look to the date, just to ID. When the ID is uniqe and we have the same ID for five following days (May 4, May 5, May 6) the values from-to will calculate on basis of the previous row.

I have :

End result should be:

Thank you.

[mahoneypat]

Please try these two column expressions to get your desired results

From = var thisrow = Metric[Metric]

var thisIDmin = CALCULATE(MIN(Metric[Metric]), ALLEXCEPT(Metric, Metric[ID]))

return IF(thisrow=thisIDmin, 0, thisIDmin)

To = Metric[Metric] + Metric[From]

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[EmaVasileva]

Hi @mahoneypat,

Thank you for the response. Any idea how the formula would like if we don't look to the date, just to ID. When the ID is uniqe and we have the same ID for five following days (May 4, May 5, May 6) the values from-to will calculate on basis of the previous row.

I have :

End result should be:




Thank you.

[v-lili6-msft · Accepted solution]

hi  @EmaVasileva 

If so, try this formula:

From = 
CALCULATE(SUM('Table'[Metric]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))+0

To = 'Table'[From]+'Table'[Metric]

 Result:

Regards,

lin

[EmaVasileva]

Hi @v-lili6-msft 

Thank you so much. It works! 

Have a great day!

[mahoneypat]

The expression I provided with the ALLEXCEPT is removing all the filters except the one on ID, so the date one is being removed now.  If you want to only look on a certain date and ID, you could add a second column into the ALLEXCEPT().

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat