Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

Calculate datediff between groups and ID


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.




Community Support
Community Support

Hi, @Siboska 


You can try the following methods.

Previous Planned =
    FILTER (
        [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.

I need something like this: 


Where Gate Name = Stage

Resolver II
Resolver II

Hi @Siboska ,

Try using this by creating new column.

DateDiff Column =
VAR PreviousDate =
        LASTDATE ( Sheet1[Planned End date] ),
        ALLEXCEPT ( Sheet1, Sheet1[Stage] ),
        Sheet1[Planned End date] < EARLIER ( Sheet1[Planned End date] )
VAR CurrentDate = Sheet1[Planned End date]
    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!


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?

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors