Forum Discussion
Measure that dynamically calculates date difference between consecutive rows in a group
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
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.
4 Replies
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.
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
ThanksHi 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.
Thank you!
