Calculate datediff between groups and ID

[Siboska]

Hi, 

I need to calculate the DateDiff between different periods grouped by ID and per group. 

Data looks like this:

The measure must caluclate the datediff on planned end date per Project ID and must be grouped on the Stage. 
So e.g. the measure must show the diff between Project ID AAA and Stage B1 10/07/2021 and Project ID AAA and Stage B1 11/07/2022 which should equal to 366 days.

I have a Index Column in the Table historical dates table wich can be used to order the rows inside each project ID.

[v-zhangti]

Hi, @Siboska 

You can try the following methods.
Column:

Previous Planned =
MAXX (
    FILTER (
        'Table',
        [Stage] = EARLIER ( 'Table'[Stage] )
            && [Planned End Date] < EARLIER ( 'Table'[Planned End Date] )
    ),
    [Planned End Date]
)

Diff = DATEDIFF([Previous Planned],[Planned End Date],DAY)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Siboska]

I need something like this: 

Where Gate Name = Stage

[Anonymous]

Hi @Siboska ,

Try using this by creating new column.

DateDiff Column =

VAR PreviousDate =

    CALCULATE (

        LASTDATE ( Sheet1[Planned End date] ),

        ALLEXCEPT ( Sheet1, Sheet1[Stage] ),

        Sheet1[Planned End date] < EARLIER ( Sheet1[Planned End date] )

    )

VAR CurrentDate = Sheet1[Planned End date]

RETURN

    IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Siboska]

Hi, 

Thx for reply. 
It is not neccesory Earlier or LastDate. The planned End date can move both backwards and forwards. So think we somehow need to incorporate the index collumn in order to tell Power BI in which order to compare?