Measure that dynamically calculates date difference between consecutive rows in a group

[anonymous3]

Hello, PowerBI community. 
I desperately need your help as this task is taking much more time than it would be worth spending on it.

I need a measure that calculates date difference between EndDate and previous StartDate in days by Group column and dynamically recalculates when particular filters are applied:

So far I tried something like this:

_DaysSinceEndElapsed = 
IF(
    SELECTEDVALUE(table[is_first_record_in_group])=True,
    BLANK(),
    DATEDIFF(
        CALCULATE(
            MAX([ExitDate]), 
            FILTER(
                ALL(table),
                table[Group] = MAX(table[Group]) &&
                table[id] < MAX(table[id])
            )
        ),
        SELECTEDVALUE(table[EndDate]),
        DAY
    )
)

but it doesn't recalculate when I apply the filters.

Please, help

[Anonymous]

Hi @anonymous3 ,

I was a little confused. In the first table, in a, the _dayssinceendelapsed value is 60 and 14? How to calculate to get the value ? And in the second table, which date was filtered so that  the result is 104? Could you pease provide more details?

Best Regards

Community Support Team _ Polly

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

[anonymous3]

Hi @Anonymous 

1. Each _DaySinceEndElapsed is calculated as date difference in days between StartDate and previous EndDate. For example, in yellow group: 

2. Then filter is applied that just filter out every second row. So I need _DaysSinceEndElapsed recalculate like this

Thanks

[Anonymous · Accepted solution]

Hi @anonymous3 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

_DaysSinceEndElapsed =
VAR _enddate =
    CALCULATE (
        MAX ( 'table'[enddate] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            'table'[Group] = SELECTEDVALUE ( 'table'[Group] )
                && 'table'[id] < SELECTEDVALUE ( 'table'[id] )
        )
    )
VAR _startdate =
    MAX ( 'table'[startdate] )
RETURN
    DATEDIFF ( _enddate, _startdate, DAY )

Then put the id column into the slicer.

If I have misunderstood your meaning, please provide more details.

Best Regards

Community Support Team _ Polly

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

[anonymous3]

Thank you!