cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors