Date difference

[karkar]

I have the results from SQL as shown below

I want to check for the following conditions . Is it possible to do it in Power Bi

From the below example we need 103 and not 101

For 103 we need to show only one record ...either the first one or the last one      

[Anonymous]

It will be possible, yes.  The answer may depend a bit on what you want to "do" with the result.

Would be easy via a calc column:

  IsValid = MyTable[value_time] - MyTable[admission] > 1 && MyTable[Value] <= 0

[karkar]

Thanks Scottsen.

So we are basically creating a new column?

Thanks for looking into it

[Anonymous]

There is a DATEDIFF function you can use, if that makes it more clear.  It just happens that subtracting 2 dates always results in a number of DAYS.  (1 day = 24 hours... thus my comparison to 1)

[karkar]

Hello,

I tried to come up with the following formulae. if the condition is true i am telling it to pull the first non blank of ID since there can be multiple rows.

my concern is that if after 24 hours we have values 1,2 or 3 along with some -1/0 like shown. We want to give credit to this kind of ID and not pull in our report. .

All we are seeing is who has -1/0 after 24 hours all through out....How can we make this happen

ID         value

101       -1--------------24th hour

101        -1

101        3

101         3-------------27th hour

24 Flag = IF(DATEDIFF(MyTable[Admission], MyTable[value_time], MINUTE) >= 1440, && MyTable[value] <=0, FIRSTNONBLANK(ID), BLANK())

Thanks

[Anonymous]

Apologies, I didn't read with care -- and didn't realize this was a problem across more than 1 row.   Lemme try again 🙂

IsValid = 
   VAR MyId = MyTable[Id]

   VAR FirstAdmission = CALCULATE(FIRSTDATE(MyTable[Admission]), FILTER(ALL(MyTable), MyTable[Id] = MyId))

   VAR BadRows = CALCULATE(COUNTROWS(MyTable),
                                          FILTER(ALL(MyTable), MyTable[Id] = MyId && 
                                                                            MyTable[value_time] > FirstAdmission + 1 &&
                                                                            MyTable[Value] > 0))
   RETURN BadRows = 0

Likely I still didn't completely understand the requirements, but hopefully this gets you pretty close... 🙂

[dkay84_PowerBI]

Depending on the number of records you WONT need, I would recommend adding your calc column in the Query Editor (this also depends on the type of connection).  Then, you can add a column for duration between the two dates and filter it, then add additional columns for your other logic and filter those.  This way, you only import the data you want to work with and it will improve the performance of your data model.

If you go the DAX route, I would follow @Anonymous's recommendation and wrap it in an IF() argument that if true, returns the First or Last value (FIRSTNONBLANK for example).