Forum Discussion

anonymous3's avatar
anonymous3
Frequent Visitor
3 years ago
Solved

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 EndDa...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
anonymous3
Frequent Visitor
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    anonymous3
    Frequent Visitor
    3 years ago

    Thank you!