Differnce Between two time Values , same Column

[NellF]

Hi All,

I am fairly new to power Bi and I want to get the Differnce between two times in the Same Column, as per the below image in excel. Can someone please help me with a formula for this. 

[Anonymous]

Hi @NellF 

Please follow these steps:

1.        index your table

2.        After moving a down one row, the two columns are subtracted. The first line outputs a blank value.

result = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Time at Build Place] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
VAR _2 = 'Table'[Time at Build Place] - _1
RETURN
    IF ( 'Table'[Index] = 1, BLANK (), _2 )

3.         final output.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[NellF]

Hi All, Thank you for your prompt answers,

But I am getting below error message, and I could not go ahead. Can you help me with this also. Many Thanks.

[Anonymous · Accepted solution]

Hi @NellF ,

Thanks for your reply, you can try to see if you are using measure instead of column. This might solve your problem.

  I have prepared two solutions to the problem:

• Use COLUMN to solve this problem

result_column = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Time at Build Place] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
VAR _2 = 'Table'[Time at Build Place] - _1
RETURN
    IF ( 'Table'[Index] = 1, BLANK (), _2 )

• Use MEASURE to solve this problem

result_measure =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Time at Build Place] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) - 1
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( 'Table'[Time at Build Place] ),
        FILTER (
            'Table',
            'Table'[Time at Build Place] = SELECTEDVALUE ( 'Table'[Time at Build Place] )
        )
    ) - _1
RETURN
    IF ( SUM ( 'Table'[Index] ) = 1, BLANK (), _2 )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Thank you for your questions and support! If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Ahmedx]

you need column index or iD & use new window functions