Duration with multiple applied filters for transactional data

[Anonymous]

Hello all,

I am working on a data model with the following columns ContractID, Stage, Start Date, End Date. ContractID can have multiple Stages and a stage can have multiple rows with Start Date and End Date (transactional data). What I'm looking to calculate is a column "Stage Duration (days)" that determines for a ContractID and specific Stage the total duration given by Min of Start Date and Max of End Date. Here is an Excel example of what the desired outcome should look like:

ContractID	Stage	Start Date	End Date	Stage Duration (days)
1	A	7/10/2020	7/11/2020	10
1	A	7/11/2020	7/13/2020	10
1	A	7/13/2020	7/20/2020	10
1	B	7/20/2020	7/24/2020	16
1	B	7/24/2020	8/5/2020	16
1	C	8/5/2020	8/6/2020	11
1	C	8/6/2020	8/8/2020	11
1	C	8/8/2020	8/12/2020	11
1	C	8/12/2020	8/16/2020	11
2	A	6/25/2020	6/28/2020	3
2	B	6/28/2020	6/30/2020	4
2	B	6/30/2020	7/2/2020	4
2	C	7/2/2020	7/13/2020	14
2	C	7/15/2020	7/15/2020	14
2	C	7/15/2020	7/16/2020	14

For example,  for ContractID=1 in Stage=A, Min of Start Date for all rows is 7/10/2020 and Max of End Date for all rows is 7/20/2020 so the difference would 10 (days).

I tried a few formulas but I couldn't find a good solution. Any advice would be helpful.

Thank you!

[DataZoe · Accepted solution]

Hi @Anonymous ,

You could try this measure:

Duration Measure =
VAR _start =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Table'[end Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
RETURN
    DATEDIFF ( _start, _end, DAY )

 You could also do this version to see a total of number of days taken too:

Duration Measure =
VAR _start =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Table'[end Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
RETURN
    COALESCE (
        DATEDIFF ( _start, _end, DAY ),
        DATEDIFF ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), DAY )
    )

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

If you need a measure, the @DataZoe ’s solution is perfect.

If you want to create a column, you can refer this formula.

Stage Duration (days) = 
var _min_date = CALCULATE(MIN('Table'[StartDate]),FILTER(ALL('Table'),EARLIER('Table'[Stage])='Table'[Stage] && EARLIER('Table'[ContractID])='Table'[ContractID]))
var _max_date = CALCULATE(MAX('Table'[EndDate]),FILTER(ALL('Table'),EARLIER('Table'[Stage])='Table'[Stage] && EARLIER('Table'[ContractID])='Table'[ContractID]))
return
DATEDIFF(_min_date,_max_date,DAY)

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

To your matrix visual, drag ContractID and Stage to the row section.  Write these measures

Start = min(data[start date])

End = max(data[end date])

Duration = [end]-[start]

Hope this helps.

[DataZoe · Accepted solution]

Hi @Anonymous ,

You could try this measure:

Duration Measure =
VAR _start =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Table'[end Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
RETURN
    DATEDIFF ( _start, _end, DAY )

 You could also do this version to see a total of number of days taken too:

Duration Measure =
VAR _start =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Table'[end Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Stage] = SELECTEDVALUE ( 'Table'[Stage] )
                && 'Table'[ContractID] = SELECTEDVALUE ( 'Table'[ContractID] )
        )
    )
RETURN
    COALESCE (
        DATEDIFF ( _start, _end, DAY ),
        DATEDIFF ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), DAY )
    )