topic Re: DateDiff with Conditions in DAX Commands and Tips

DateDiff with Conditions

usomaraju — Thu, 27 Feb 2020 22:16:36 GMT

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

Re: DateDiff with Conditions

Anonymous — Fri, 28 Feb 2020 01:26:45 GMT

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!!

Re: DateDiff with Conditions

Greg_Deckler — Fri, 28 Feb 2020 02:56:43 GMT

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.

Re: DateDiff with Conditions

usomaraju — Fri, 28 Feb 2020 15:29:41 GMT

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.

Re: DateDiff with Conditions

Anonymous — Sat, 29 Feb 2020 00:46:28 GMT

Can you share plz your dataset? No as image.

Thanks

Re: DateDiff with Conditions

Greg_Deckler — Sat, 29 Feb 2020 16:26:53 GMT

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/339586.

I have attached the PBIX file.

Re: DateDiff with Conditions

usomaraju — Mon, 02 Mar 2020 15:42:07 GMT

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