cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 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 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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 )

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.

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

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.