topic Re: Measure that dynamically calculates date difference between consecutive rows in a group in DAX Commands and Tips

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

anonymous3 — Mon, 12 Sep 2022 16:13:41 GMT

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

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

Anonymous — Tue, 13 Sep 2022 01:42:26 GMT

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.

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

anonymous3 — Tue, 13 Sep 2022 08:27:09 GMT

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

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

Anonymous — Tue, 13 Sep 2022 08:50:27 GMT

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.

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

anonymous3 — Tue, 13 Sep 2022 09:33:47 GMT

Thank you!