Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Differnce Between two time Values , same Column

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. 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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.

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    you need column index or iD & use new window functions

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Please follow these steps:

    1.        index your table

    1.        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 )
    1.         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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        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.