Forum Discussion
Helper ICalculate 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 value will be 0, since the diff is already >0.2, for 30/04/21 21:47 the value will be 1, cause 0.03468% is not enough, for 30/04/21 21:46 the value will be 2, cause even 1 minute later the sum will not reach required condition.
| date | Diff% | |
| 30/04/21 21:49 | -0.16% | |
| 30/04/21 21:48 | 0.22% | |
| 30/04/21 21:47 | 0.03% | |
| 30/04/21 21:46 | 0.00% | |
| 30/04/21 21:45 | 0.10% |
3 Replies
- Jihwan_Kim
Super User
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
xxenossUnfortunately 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.
Hi xxenoss,
So you mean the diff field is DAX expression that calculated by on your categories?
If that is the case, can you please share some dummy data(with raw table schema and Dax expressions) and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
