Counting duration between a time based on fixed value and time based on variable value

[Anonymous]

Hi All,

Below is a sample of data in Power BI that records temperature every minute under 'Temprature'

Basically I want to measure how long it takes in minutes for the temp to go from it's highest value (96) to a temperature of 55 (or the closest below). I'm not interested in the time before the max temp. 

I've tried a few things including creating a column that ranks the date/ time and filtering for numbers greater than that which represent the max temp but I've been getting a lot of circular dependency errors. Also, in the real data theres multiple 'Orders' but I guess I can use allexept to deal with that.

Any help is greatly appreciated

Thank you in advance!

[v-henryk-mstf]

Hi @Anonymous ,

You can refer to a formula similar to the following:

M = 
VAR min_ =
    CALCULATE (
        MINUTE ( MAX ( Sheet1[Date/Time] ) ),
        FILTER (
            ALL ( Sheet1 ),
            ABS ( MAX ( Sheet1[Value] ) - 55 )
                < ABS ( ( Sheet1[Value] ) - 55 )
                && MAX ( Sheet1[Item] ) = Sheet1[Item]
        )
    )
VAR max_ =
    CALCULATE (
        MINUTE ( MAX ( Sheet1[Date/Time] ) ),
        FILTER (
            ALL ( Sheet1 ),
            ABS ( MAX ( Sheet1[Value] ) - 96 )
                < ABS ( ( Sheet1[Value] ) - 96 )
                && MAX ( Sheet1[Item] ) = Sheet1[Item]
        )
    )
RETURN
    MAX_ - min_

Also I am a little confused as to which time point to base on for having multiple identical time points close to 55. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @v-henryk-mstf ,

Thank you for your help, I've managed to do it but for your question I want to base the time off the closest value >= 55. So if there were values of 55.1, 54.9, 54.7 then it would use the time relating to the value of 54.9 as long as it's on the latest side of the max temp.

Below is what I did and the resulting dates.

Date diff =

var _maxvalue = MAX(Sheet1[Value])

var _maxdate = CALCULATE(MIN(Sheet1[Date/Time]), Sheet1[Value] = _maxvalue)

var _enddatelower = CALCULATE(MIN(Sheet1[Date/Time]),

Sheet1[Value] <= 55, Sheet1[Date/Time] > _maxdate)

return

DATEDIFF(_maxdate, _enddatelower,MINUTE) -1

 Item 101 = 51 mins, Item 102 = 53 mins

Thank you. 

[v-henryk-mstf]

Hi @Anonymous ,

Thank you very much for your feedback and providing valuable reference responses.😊

Whether the problem has been solved, if the problem has been solved you can mark the reply for the standard answer. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[Anonymous]

Hi @lbendlin ,

See below for sample data. For 101 I'm looking for the countrows/mins between max (2:36) & 55 value (3:28). For 102 this is 12:26 and 13:19 respectively.

Thank you
Sample