How to exclude oldest timestamp from CALCULATE ( MIN (... function

[Anonymous]

I have a column with modified timestamps. The goal is to write the earliest modification date for every ID BUT exclude the very first one which is the same as ticket creation date. So I need to exclude the timestamp where the time in MUOKATTU (*modified*)is the same as LUOTU -column

I have tried using IF etc. but the problem is that I need that EARLIER to work correctly. Please advice.

Reaction-Timestamp =
CALCULATE(
    MIN(
      'Tickets'[Modified]);
      FILTER('Tickets';'Tickets'[ID]  = EARLIER('Tickets'[ID])
   )
  )

[v-shex-msft]

Hi @Anonymous,

Baed on your descriptiont, you want to get the current id, right?

If this is a case, you can take a look at below formula:

MinTimeStamp=MINX(FILTER(ALL('Tickets');'Tickets'[ID]=MAX('Tickets'[ID]));'Tickets'[Modified])

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

To open up my scenario:

- I have multiple rows with same ID (One row for every modification)

- I have created 2 additional columns. One for First timestamp and other for lates.

- I want to write the same stamp for every row (Earliest for Reaction-timestamp and Latest for Resolution-Tmes

The resolution (Latest) works like I want BUT I have a problem with the first (Reaction). The earliest timestamp is in fact the same as the creation date timestamp (Luotu). I mean that the same timestamp is also recorded on Modified-column (Muokattu). So I would nee the function to exclude the earliest timestamp and write the second earliest timestamp for every row in Reaction-Timestamp

Ofcourse For every ID. I hope this clarifies the case. Thanks for your inputs.

[Anonymous]

So for me "reaction time" means the time duration from ticket creation to first modification done to the ticket (first input from support agent).

[v-shex-msft]

Hi @Anonymous,

Sorry for misunderstanding, I modified the formula to remove the records which has the same timestamp.

MinTimeStamp=MINX(FILTER(ALL('Tickets');'Tickets'[ID]=MAX('Tickets'[ID])&&'Tickets'[Muokuttu]<>'Tickets'[Luotu]);'Tickets'[Muokuttu])

Regards,

Xiaoxin Sheng

[Anonymous]

The function is working but giving only empy rows. I have had the same problem when trying to manage this with EARLIER. What have happened is that modified is compared to every creation date in table and not to within the spesified ID only. So function compares to last possible creation date and filter fails every time -> No entries to rows.

I don't completely understand how your function works so this might something else as well. Any ideas?

[Anonymous]

I tried adding ID to comparation and now it gives the last possible timestamp. Just to play around and maybe give some ideas.

MINX(FILTER(ALL(Tukipyynnot);Tukipyynnot[ID]=MAX(Tukipyynnot[ID])&&Tukipyynnot[Muokattu]<>(Tukipyynnot[ID])&&Tukipyynnot[Luotu]);Tukipyynnot[Muokattu])

[v-shex-msft]

Hi @Anonymous,

Can you share us some sample data to test?

Regards,

Xiaoxin Sheng

[Anonymous]

Please find the demodata project attached. I also found out fow the function works as I was creating the demo. It seems to pick the second earliest timestamp from the last ID found in table and not for every ID like my RESOLUTION-function does.

         ID      CREATED              MODIFIED              REACTION                 RESOLUTION

DemoData (ZIP)

[v-shex-msft · Accepted solution]

Hi @Anonymous,

My formula is a measure, if you put it in a calculated column, it may get the wrong result.(measure will limit the calculate range to row, but calculate column will calculate the full column)

Measure:

Reaction-Timestamp(Measure) = MINX(FILTER(ALL(Tickets),Tickets[ID]=MAX(Tickets[ID])&&Tickets[Modified]<>Tickets[Created]),Tickets[Modified])

BTW, if you want to use calculate column, you can try to use below formula:

Reaction-Timestamp = 
MINX(FILTER(ALL(Tickets),Tickets[ID]=EARLIER(Tickets[ID])&&Tickets[Modified]<>Tickets[Created]),Tickets[Modified])

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you @v-shex-msft! With your help I got it working.

However I ran into problem which is obvious when I think about it now...

I opened a previous discussion regarding this project here:

https://community.powerbi.com/t5/Desktop/Reaction-Resolution-time-calculations-with-a-twist/m-p/1126...

The problem in PowerBI seems to be that there is not versatile enough time calculation functions available so I used the following function to show reaction/resolution times as I wanted. However it is ofcourse a text column which I cannot use in any graphs / calculate averages etc.

I have to think my abroach again so I can ask exact questions but in case you get hold of my problem of course all hints are more than welcome. But this question/opening got answered and I marked your post as solution. Thanks again!

----

Resolution-Display3 =

VAR Duration = [Resolutionmeasure-seconds] // 86400 seconds in a day
VAR Days =
    INT ( Duration / 86400 ) //  3600 seconds in a hour
VAR Hours =
    INT ( MOD ( Duration - ( days * 3600 ); 3600 ) / 60 ) // There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD ( Duration - ( Hours * 3600 ); 3600 ) / 60 ) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
    ROUNDUP ( MOD ( MOD ( Duration - ( Hours * 3600 ); 3600 ); 60 ); 0 ) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR D =
    IF ( LEN ( Days ) = 1; CONCATENATE ( "0"; Days ); CONCATENATE ( ""; Days ) )
VAR H =
    IF ( LEN ( Hours ) = 1; CONCATENATE ( "0"; Hours ); CONCATENATE ( ""; Hours ) ) // Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1;
        CONCATENATE ( "0"; Minutes );
        CONCATENATE ( ""; Minutes )
    ) // Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1;
        CONCATENATE ( "0"; Seconds );
        CONCATENATE ( ""; Seconds )
    ) // Now return hours. minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        D;
        CONCATENATE ( ":"; CONCATENATE ( H; CONCATENATE ( ":"; M ) ) )
    )