Forum Discussion

Siboska's avatar
Siboska
Helper II
3 years ago

Calculate datediff between groups and ID

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.


 

 

 

Help appreciated
Need Help
Need Help DAX

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Siboska
      Helper II
      3 years ago

      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?

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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's avatar
      Siboska
      Helper II
      3 years ago

      I need something like this: 

      Where Gate Name = Stage