topic Re: Number of days between 2 dates in the same column in DAX Commands and Tips

Number of days between 2 dates in the same column

ZR1 — Mon, 25 Mar 2024 15:16:54 GMT

I am looking to calculate the number of days between different Status's. This will beed to be calculated based on a 24 hour basis too so if this is more than 24 hours, if should be counted as another day. The dates are in the same column.

See below for example table

ID	Date	Status
1	20/03/2024	Completed
2	13/03/2024	Submitted
1	10/03/2024	Sumbitted
2	21/03/2024	Completed
1	16/03/2024	Approval Needed
2	18/03/2024	Approval Needed

This is what I want it to show

I would also like some help on not counting weekends and bank holiday days. Can this be done?

amitchandak Greg_Deckler lbendlin AmiraBedh

Thanks

Re: Number of days between 2 dates in the same column

Greg_Deckler — Mon, 25 Mar 2024 15:13:47 GMT

ZR1 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.

Also, if you can post that data as text it would be very helpful in getting to a solution.

Re: Number of days between 2 dates in the same column

ZR1 — Mon, 25 Mar 2024 15:17:32 GMT

Just amended the post to include the table as text

Re: Number of days between 2 dates in the same column

Greg_Deckler — Mon, 25 Mar 2024 15:58:08 GMT

ZR1 PBIX is attached. Here is the measure though:

Days = VAR __ID = MAX('Table'[ID]) VAR __Pair = MAX('Pairs'[Value]) VAR __Start = SWITCH( __Pair, "Submitted - Approval Needed", MINX(FILTER('Table', [ID] = __ID && [Status] = "Submitted"), [Date ]), "Approval Needed - Completed", MAXX(FILTER('Table', [ID] = __ID && [Status] = "Approval Needed"), [Date ]), BLANK() ) VAR __End = SWITCH( __Pair, "Submitted - Approval Needed", MINX(FILTER('Table', [ID] = __ID && [Status] = "Approval Needed"), [Date ]), "Approval Needed - Completed", MAXX(FILTER('Table', [ID] = __ID && [Status] = "Completed"), [Date ]), BLANK() ) VAR __Result = ( __End - __Start ) * 1. RETURN __Result

Re: Number of days between 2 dates in the same column

ZR1 — Thu, 28 Mar 2024 11:25:33 GMT

Greg_Deckler Thank you so much

There is another part to this, as we have created the numbers of days for the Pairs, I now need to count the number of ID's which have been less than 1 day, 1-2 days, 2-3, 3-4, 4-5 and 5+ etc

This would then need to exclude the weekends and also babk holidays, anyway this can be done?

Thanks again

Re: Number of days between 2 dates in the same column

Greg_Deckler — Fri, 29 Mar 2024 12:47:49 GMT

ZR1 Well, there is a NETWORKDAYS function in DAX now that should simplify that.