cancel
Showing results for
Did you mean:

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

Helper II

## 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.

4 REPLIES 4
Community Support

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.

Helper II

I need something like this:

Where Gate Name = Stage

Resolver II

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)

Helper II

Hi,

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?

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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