Number of days between 2 dates in the same column

[ZR1]

Hi

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

[ZR1]

Just amended the post to include the table as text

[Greg_Deckler · Accepted solution]

@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

[ZR1]

@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

[Greg_Deckler]

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

[Greg_Deckler]

@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/3395....
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.