Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Gillard_2
Frequent Visitor

Calculated DateDiff between various rows

I need a calculated column that will give me the duration between various rows:

Gillard_2_0-1625228632514.png

The table above will always have either one or two records for each unique code.

It will always have when the record was created and if a certain action has been completed it will show a second row of date.

I need a calculated column that will show the DateDiff between the two records that have the same code in the left column.

The versions will always be 1 for the first record but the second record could be anything.

The above table only have two records that have both lines of data so I would expect to see two of the rows in the new column with data and the rest would be blank as the action has not been completed yet.

Hope this makes sense but I am not sure how I can do this.

Thanks in advance

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Gillard_2 ;

According to your description, you also could try to create a column as follows:

datediff =
IF (
    CALCULATE ( DISTINCTCOUNT ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = 2
        && CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = [TIME],
    DATEDIFF (
        CALCULATE ( MIN ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        DAY
    )
)

The final output is shown below:

vyalanwumsft_0-1625474828390.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @Gillard_2 ;

According to your description, you also could try to create a column as follows:

datediff =
IF (
    CALCULATE ( DISTINCTCOUNT ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = 2
        && CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = [TIME],
    DATEDIFF (
        CALCULATE ( MIN ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        DAY
    )
)

The final output is shown below:

vyalanwumsft_0-1625474828390.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ryan_mayu
Super User
Super User

@Gillard_2 

Is this what you want?

Column = 
var LAST=MAXX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[TIME]<EARLIER('Table'[TIME])),'Table'[TIME])
RETURN IF(ISBLANK(LAST),BLANK(),DATEDIFF(LAST,'Table'[TIME],DAY))

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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