DateDiff with Conditions

[usomaraju]

Hi,
Can someone help me out with the datediff measure with Conditions

I need to calculate the datediff based on Response code i.e the first occurrence of 500 and the next occurrence of 200, that means the Time diff between Index ‘0’ row and index ‘4’ row (2/22/2020 17:23 - 2/22/2020 17:12 = 11 min)

Event time	Response code	Request Name	Index
2/22/2020 17:12	500	POST Write/CaseSavedEvent	0
2/22/2020 17:13	500	POST Write/CaseSavedEvent	1
2/22/2020 17:14	500	POST Write/CaseSavedEvent	2
2/22/2020 17:15	500	POST Write/CaseSavedEvent	3
2/22/2020 17:23	200	POST Write/CaseSavedEvent	4

[Greg_Deckler]

Well, this seems like an issue that would involve EARLIER. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Also, if you want the difference in days, you can always just subtract the dates and multiply by 1.

[Anonymous]

Hello!!

Measure = DATEDIFF(CALCULATE(MAX(Hoja1[Event time]);Hoja1[Response code] =200 );CALCULATE(MIN(Hoja1[Event time]);Hoja1[Response code] = 500);MINUTE)

Hope this helps!!

Regards!! 

[usomaraju]

Hi , 

The Query that you given did not work,it gives the null values.

here i'm posting my complete table, can you please help on this.

We are trying to calculate time difference between (first occurance of 500 - first occurance of 200) and continue the same for the next occurance of 500 and 200.

example:event time of index '0' - event time of index '4' 

event time of index '8' - event time of index '9' 

and so forth.

[Greg_Deckler · Accepted solution]

Well, you have two problems here. First, you need to flag the correct 500 rows to do your calculation on. Second, you need to do your calculation.

The first part is something like this:

IsFirst500After200 = 
    VAR __Table500 = FILTER(ALL('Table'),[Response code] = 500 && 'Table'[Index]<EARLIER('Table'[Index]))
    VAR __Table200 = FILTER(ALL('Table'),[Response code] = 200 && 'Table'[Index]<EARLIER('Table'[Index]))
    VAR __Max500 = MAXX(__Table500,[Index])
    VAR __Max200 = MAXX(__Table200,[Index])    
    VAR __Flag = 
        SWITCH(TRUE(),
            [Response code] = 200,FALSE(),
            __Max500 > __Max200,FALSE(),
            __Max500 < 'Table'[Index] && ISBLANK(__Max200),FALSE(),
            TRUE()
        )
RETURN
    __Flag

The second part:

Duration = 
    IF(
        [IsFirst500After200],
        VAR __Next200 = MINX(FILTER(ALL('Table'),'Table'[Index] > EARLIER('Table'[Index]) && 'Table'[Response code] = 200),[Event time])
        RETURN DATEDIFF([Event time],__Next200,SECOND),
        BLANK()
    )

This second part is the identical technique used in MTBF. http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

I have attached the PBIX file. 

[Anonymous]

Can you share plz your dataset? No as image.

Thanks

[usomaraju]

Hi, 

Here is the data not in the form of image

Event time	Response code	Request Name	Index
2/22/2020  17:12:19 PM	500	POST Write/CaseSavedEvent	0
2/22/2020  17:13:19 PM	500	POST Write/CaseSavedEvent	1
2/22/2020  17:14:20 PM	500	POST Write/CaseSavedEvent	2
2/22/2020  17:15:46 PM	500	POST Write/CaseSavedEvent	3
2/22/2020  17:23:15 PM	200	POST Write/CaseSavedEvent	4
2/22/2020  17:24:15 PM	200	POST Write/CaseSavedEvent	5
2/22/2020  17:25:15 PM	200	POST Write/CaseSavedEvent	6
2/22/2020  17:26:15 PM	200	POST Write/CaseSavedEvent	7
2/22/2020  18.30:15 PM	500	POST Write/CaseSavedEvent	8
2/22/2020  18:33:15 PM	200	POST Write/CaseSavedEvent	9
2/22/2020  18:43:15 PM	500	POST Write/CaseSavedEvent	10
2/22/2020  18:44:15 PM	200	POST Write/CaseSavedEvent	11
2/22/2020  18:45:15 PM	200	POST Write/CaseSavedEvent	12