Calculating Duration of episodes of same type

[Elscc]

I have a person table and an episodes table. (1 to many)

The episodes table have back to back episodes (end date of last date = start date of next episode), but can vary by type. 

Person ID	Type	Start Date	End Date
1	a	01/01/2020	02/02/2020
1	b	02/02/2020	03/03/2020
1	a	03/03/2020	04/04/2020
1	a	04/04/2020
2	a	01/01/2020	02/02/2020
2	a	02/02/2020	03/03/2020
2	c	04/04/2020	05/05/2020

I would like to find the duration of a persons latest group of episodes of the same type. Where end date is null, it needs to be from today. it also needs to exclude the earliest date where the type is the same as the current type, but there has been some episodes of a different type in between. 

for example person 1 would need the duration from 03/03/2020 to today

person 2 would need duration from 01/01/2020 to 03/03/2020

I will likely be adding this value to a calculated column on the person table

I have tried using power query to make a grouped index, and i think this is the correct route, but have not been able to get it to work.

I hope this is suffecient information but please let me know if more is required.

All help is hugely appreciated.

Thanks

[v-zhouwen-msft · Accepted solution]

Hi @hackcrr ,thanks for the quick reply, I'll add further.

Hi @Elscc ，

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Index = 
VAR _a = [Start Date]
VAR _b = [Person ID]
VAR _c = COUNTROWS(FILTER('Table','Table'[Person ID] = _b && 'Table'[Start Date] <= _a))
RETURN _c

2. Use the following DAX expression to create a measure

MEASURE =
VAR _a =
    MAXX (
        SUMMARIZE ( 'Table', [Person ID], "MaxDate", MAX ( 'Table'[Start Date] ) ),
        [MaxDate]
    ) //Maximum start date after grouping by id
VAR _b =
    LOOKUPVALUE (
        'Table'[Type],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Type corresponding to the maximum start date
VAR _c =
    SWITCH (
        TRUE (),
        _b = "a",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "b" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        //Find the index corresponding to the maximum start date of another type
        _b = "b",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "a" && 'Table'[Start Date] < _a ),
                [Index]
            )
    )
VAR _d =
    LOOKUPVALUE (
        'Table'[Index],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Index corresponding to the maximum start date
VAR _StartDate =
    IF (
        ISBLANK ( _c ),
        MINX (
            FILTER ( 'Table', 'Table'[Person ID] = SELECTEDVALUE ( 'Table'[Person ID] ) ),
            [Start Date]
        ),
        IF (
            _d - _c = 1,
            _a,
            LOOKUPVALUE (
                'Table'[Start Date],
                'Table'[Index], _c + 1,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        )
    )
VAR _EndDate =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                'Table'[End Date],
                'Table'[Start Date], _a,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        ),
        TODAY (),
        LOOKUPVALUE (
            'Table'[End Date],
            'Table'[Start Date], _a,
            'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
        )
    )
RETURN
    DATEDIFF ( _StartDate, _EndDate, DAY )

3.Final output

[v-zhouwen-msft · Accepted solution]

Hi @hackcrr ,thanks for the quick reply, I'll add further.

Hi @Elscc ，

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Index = 
VAR _a = [Start Date]
VAR _b = [Person ID]
VAR _c = COUNTROWS(FILTER('Table','Table'[Person ID] = _b && 'Table'[Start Date] <= _a))
RETURN _c

2. Use the following DAX expression to create a measure

MEASURE =
VAR _a =
    MAXX (
        SUMMARIZE ( 'Table', [Person ID], "MaxDate", MAX ( 'Table'[Start Date] ) ),
        [MaxDate]
    ) //Maximum start date after grouping by id
VAR _b =
    LOOKUPVALUE (
        'Table'[Type],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Type corresponding to the maximum start date
VAR _c =
    SWITCH (
        TRUE (),
        _b = "a",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "b" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        //Find the index corresponding to the maximum start date of another type
        _b = "b",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "a" && 'Table'[Start Date] < _a ),
                [Index]
            )
    )
VAR _d =
    LOOKUPVALUE (
        'Table'[Index],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Index corresponding to the maximum start date
VAR _StartDate =
    IF (
        ISBLANK ( _c ),
        MINX (
            FILTER ( 'Table', 'Table'[Person ID] = SELECTEDVALUE ( 'Table'[Person ID] ) ),
            [Start Date]
        ),
        IF (
            _d - _c = 1,
            _a,
            LOOKUPVALUE (
                'Table'[Start Date],
                'Table'[Index], _c + 1,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        )
    )
VAR _EndDate =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                'Table'[End Date],
                'Table'[Start Date], _a,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        ),
        TODAY (),
        LOOKUPVALUE (
            'Table'[End Date],
            'Table'[Start Date], _a,
            'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
        )
    )
RETURN
    DATEDIFF ( _StartDate, _EndDate, DAY )

3.Final output

[Elscc · Accepted solution]

Hi @v-zhouwen-msft 

I have got the code to work by making an adjustment to VAR _c as below.

Once again I really appreciate you taking the time to assist. 

MEASURE =
VAR _a =
    MAXX (
        SUMMARIZE ( 'Table', [Person ID], "MaxDate", MAX ( 'Table'[Start Date] ) ),
        [MaxDate]
    ) //Maximum start date after grouping by id
VAR _b =
    LOOKUPVALUE (
        'Table'[Type],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Type corresponding to the maximum start date
VAR _c =
    SWITCH (
        TRUE (),
        _b = "a",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "a" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        //Find the index corresponding to the maximum start date of another type
        _b = "b",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "b" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        _b = "c",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "c" && 'Table'[Start Date] < _a ),
                [Index]
            )
    )
VAR _d =
    LOOKUPVALUE (
        'Table'[Index],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Index corresponding to the maximum start date
VAR _StartDate =
    IF (
        ISBLANK ( _c ),
        MINX (
            FILTER ( 'Table', 'Table'[Person ID] = SELECTEDVALUE ( 'Table'[Person ID] ) ),
            [Start Date]
        ),
        IF (
            _d - _c = 1,
            _a,
            LOOKUPVALUE (
                'Table'[Start Date],
                'Table'[Index], _c + 1,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        )
    )
VAR _EndDate =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                'Table'[End Date],
                'Table'[Start Date], _a,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        ),
        TODAY (),
        LOOKUPVALUE (
            'Table'[End Date],
            'Table'[Start Date], _a,
            'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
        )
    )
RETURN
    DATEDIFF ( _StartDate, _EndDate, DAY )

[Elscc]

Hi @v-zhouwen-msft Thanks for taking the type to reply. 

I think this is going to work, but in my situation I actually have 3 types of "Type". Apologies for not making that clear. How would I amend VAR _c to account for a third type, such as "c"?

Thanks

[Elscc]

Anyone else have any advice?

Thanks in advance

[hackcrr]

Hi, @Elscc 

First, we'll use Power Query to transform the data and then use DAX to create a calculated column for the duration of each person's latest set of episodes of the same genre. Power Query Steps.
1. sort by Person ID and Start Date.
2. Add the Index column.
3.Add the Last Episode Type column.
4. Add Group column.
5. Fill in the Group column.
The following is a reference M code:

let
    Source = YourSourceTable,
    SortedTable = Table.Sort(Source,{{"Person ID", Order.Ascending}, {"Start Date", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
    PreviousType = Table.AddColumn(AddedIndex, "Previous Type", each if [Index] = 0 then null else try AddedIndex[Type]{[Index]-1} otherwise null),
    Group = Table.AddColumn(PreviousType, "Group", each if [Type] = [Previous Type] then null else [Index]),
    FillDownGroup = Table.FillDown(Group,{"Group"})
in
    FillDownGroup

Create a duration calculation column, below is a reference DAX:

Latest Episode Duration = 
VAR LatestGroup = 
    CALCULATE(
        MAX('Episodes'[Group]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID])
        )
    )

VAR StartDate = 
    CALCULATE(
        MIN('Episodes'[Start Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID]) &&
            'Episodes'[Group] = LatestGroup
        )
    )

VAR EndDate = 
    CALCULATE(
        MAX('Episodes'[End Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID]) &&
            'Episodes'[Group] = LatestGroup
        )
    )

RETURN
    IF(
        ISBLANK(EndDate),
        DATEDIFF(StartDate, TODAY(), DAY),
        DATEDIFF(StartDate, EndDate, DAY)
    )

The results are shown below:

If you want to add it as a calculated column to the Person table, then the DAX is as follows:

Latest Episode Duration = 
VAR PersonID = 'Person'[Person ID]

VAR LatestGroup = 
    CALCULATE(
        MAX('Episodes'[Group]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID
        )
    )

VAR StartDate = 
    CALCULATE(
        MIN('Episodes'[Start Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID &&
            'Episodes'[Group] = LatestGroup
        )
    )

VAR EndDate = 
    CALCULATE(
        MAX('Episodes'[End Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID &&
            'Episodes'[Group] = LatestGroup
        )
    )

RETURN
    IF(
        ISBLANK(EndDate),
        DATEDIFF(StartDate, TODAY(), DAY),
        DATEDIFF(StartDate, EndDate, DAY)
    )

Best Regards,

hackcrr

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

[Elscc]

Hi @hackcrr ,

Thanks for taking the time to respond, but unfortunately the added power query steps cause the query to take too long to load. It loads at around 8 rows per second, and with the number of rows I have it would take an hour to load the query. So I havent been able to test the dax columns to verify if it works how I need.

I dont know what causes the performance issues (loads in a few seconds before the changes), or if there are workarounds, but currently I cannot use this solution. 

Thanks

[hackcrr]

Hi, @Elscc 

You can try the following code without creating metrics. You can take out a part of your dataset and try the approach in my two replies. See if you can get the expected results.

let
    Source = YourEpisodesTable, // Replace YourEpisodesTable with the name of your Episodes table
    Grouped = Table.Group(Source, {"Person ID", "Type"}, {{"Grouped", each _, type table}}),
    AddedCustom = Table.AddColumn(Grouped, "Duration", each 
        let
            Type = [Type],
            Episodes = [Grouped],
            LastEpisode = List.Last(Episodes),
            LastEndDate = if LastEpisode[End Date] = null then Date.From(DateTime.LocalNow()) else LastEpisode[End Date],
            PreviousEpisodes = List.RemoveLastN(Episodes, 1),
            EarliestDifferentTypeDate = List.First(List.Select(PreviousEpisodes, each _[Type] <> Type), each true),
            Duration = Duration.Days(Duration.From(LastEpisode[Start Date] - (if EarliestDifferentTypeDate <> null then EarliestDifferentTypeDate[End Date] else Episodes{0}[Start Date])))
        in
            Duration
    ),
    Expanded = Table.ExpandTableColumn(AddedCustom, "Grouped", {"Person ID", "Type", "Start Date", "End Date"}, {"Person ID", "Type", "Start Date", "End Date"}),
    RemovedColumns = Table.RemoveColumns(Expanded, {"Grouped"})
in
    RemovedColumns

Best Regards,

hackcrr

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

[Elscc]

Hi @hackcrr ,

Thanks again for your time. I have copied your code into the advanced editor but getting a few errors. 
Firstly error is that Person ID and Type are duplicate names - this occurs within the "Expanded" step, but is not a problem as i can just change them.

After this i get an error:

This appears to be in the "RemovedColumns" step, but if I remove this I get errors for all the "Duration" Values:

I hope this helps troubleshoot. Once again I appreciate you taking the time to help me.

Thanks