Number of days at each stage

[smithub]

Hi all, I want to calculate the number of days between each stage of an application. 

Earliest FirstDate column shows the first date that the application went into each category. The categories listed above have subcategories, that's why there are more than one date for each category. I want to calculate the number of days between the first date of each category. You will seen a summary for Case ID showing first dates for each category.

For example I want to show:

Post AIP- Pre App days as Apps in Preparation - Post AIP- Pre App 

Apps in Preparation days as Apps With Credit - Apps in Preparation

Apps With Credit days as Drawdowns - Apps With Credit

Drawdowns as total of all category days or Drawdowns - Apps in Preparation.

Thanks all

[smithub]

Can anyone help out with this issue please? It looks like an easy fix for a Power BI expert.

Thank you.

[smithub]

Really appreciate if anyone could help me on this issue. Thanks a mil

[smithub]

How can I include 'Sort Order' in the formula above as it's a column in a table called 'Stage Order'. See below source tables for each column.

Table	Column
Status Activity	Case ID
Status Activity	Stage
Stage Order	Sort Order
Status Activity	Status Date
Status Activity	Earliest FirstDate

[vibin_k]

You can Create a new column to get the Data like this 

and the code used to create the Column is 

DayDifference =

VAR SelectedStage = 'Table'[Sort Order]

VAR NextStage = SelectedStage + 1

VAR SelectedDate = 'Table'[Status Date]

VAR EndingDateOfCurrentStage =

CALCULATE(

    MINX(

        FILTER(

            ALL('Table'),

            'Table'[Sort Order] = SelectedStage &&

            'Table'[Status Date] <= SelectedDate

        ),

        'Table'[Status Date]

    ),

    'Table'[Sort Order] = SelectedStage

)

VAR StartingDateOfNextStage =

CALCULATE(

    MINX(

        FILTER(

            ALL('Table'),

            'Table'[Sort Order] = NextStage &&

            'Table'[Status Date] >= EndingDateOfCurrentStage

        ),

        'Table'[Status Date]

    ),

    'Table'[Sort Order] = NextStage

)

RETURN

IF(ISBLANK(StartingDateOfNextStage), 0, StartingDateOfNextStage - EndingDateOfCurrentStage)

[amitchandak]

@smithub , Based On what I got

Measure for time between Stage

measure =

Var _date = Minx(filter(allselected(Table), [Case ID] = Max([Case ID]) && [Sort Order] = Max([Sort Order]) ), [Status Date])

Var _last = Maxx(filter(allselected(Table), [Case ID] = Max([Case ID]) && [Sort Order] < Max([Sort Order]) ), [Sort Order])

Var _lastDate = Maxx(filter(allselected(Table), [Case ID] = Max([Case ID]) && [Sort Order] =_last ), [Status Date])

return

datediff(_lastDate ,_date , day)

[smithub]

My sort order column is coming from a different source table called 'Stage Order'. I'm new to Power BI. How can I include Sort Order in the formula?

measure =

Var _date = Minx(filter(allselected('Status Activity'), [Case ID] = Max([Case ID]) && [Sort Order] = Max('Stage Order'[Sort Order]) ), [Status Date])

Var _last = Maxx(filter(allselected('Status Activity'), [Case ID] = Max([Case ID]) && [Sort Order] < Max('Stage Order'[Sort Order]) ), [Sort Order])

Var _lastDate = Maxx(filter(allselected('Status Activity'), [Case ID] = Max([Case ID]) && [Sort Order] =_last ), [Status Date])

return

datediff(_lastDate ,_date , day)