Forum Discussion

xxenoss's avatar
xxenoss
Icon for Helper I rankHelper I
5 years ago

Calculate minimum time required to match a certain condition

Hi everyone, I have following dataset. For every given moment I need to find the mimum number of minutes to pass, where sum of Diff% in betwin, would be >=0.2. For example for 30/04/21 21:48 this val...
Need Help
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, xxenoss 

I am not sure whether I correctly understood your question.

I assumed two things.

- The SUM you mentioned above is not cumulate-sum.

- The 0.2 you mentioned above is the threshold and you want to find the minimum time that reached the threshold first.

 

Please check the below picture and the sample pbix file's link down below.

 

 

Result =
VAR greaterequaltothreshold =
CALCULATE (
MIN ( 'Table'[date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Diff%] >= 0.002 )
)
VAR result =
DATEDIFF ( MAX ( 'Table'[date] ), greaterequaltothreshold, MINUTE )
RETURN
IF ( ISFILTERED ( 'Table'[date] ), result )

 

https://www.dropbox.com/s/6pztr70jftwuvgh/xxenoss.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

xxenoss's avatar
xxenoss
Icon for Helper I rankHelper I
5 years ago

Unfortunately I need cumulate-sum between those periods. For examle if we will extend the data with 30/04/21 21:44  0.12%, the value for this row will be 1, cause 0.12%+0.10%>=0.22. And for the first row 30/04/21 21:49 the value will be blank.