Forum Discussion

HelenaX's avatar
HelenaX
Helper I
5 years ago
Solved

Create a Measure to Calculate Time Duration

Could someone please tell me how to create a measure to calculate a time duration? I was able to calculate a time duration (called "CCP Duration" in below screen shot) by creating a new column with below formula:

 

CCP Duration = DATEDIFF(CCP_STATUS[CCP Start Time], CCP_STATUS[CCP Finish Time], minute)
 
which is basically subtracting the "CCP Start Time" column from the "CCP Finish Time" column:
 

 

 

However, when I tried to create a measure by using the Quick Measure, it doesn't use the actual value of the Finish or Start Time but used Count
 

 

So, how do I create a measure to simply replicating the formula of 

CCP Duration = DATEDIFF(CCP_STATUS[CCP Start Time], CCP_STATUS[CCP Finish Time], minute)

 

I don't know about DAX and found it difficult to do some pretty simple calculations in PowerBI. 

 

I would really appreciate your help. 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    HelenaX , If you need duration you can create a new column in power query or dax like

     

    diff Duration = CCP_STATUS[CCP Finish Time] - CCP_STATUS[CCP Start Time]

    • HelenaX's avatar
      HelenaX
      Helper I
      5 years ago

      amitchandak This is the formula when creating a new column. I need to create a Measure to calculate the duration. Could you please advise how to create a measure?

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        HelenaX , In case they are from same table

        diff Duration = max(CCP_STATUS[CCP Finish Time]) - min(CCP_STATUS[CCP Start Time])

         

         

        diff Duration = sumx(CCP_STATUS,datediff(CCP_STATUS[CCP Finish Time]) - min(CCP_STATUS[CCP Start Time]), minute))

         

         

        diff Duration = sumx(values(CCP_STATUS[ID column]),datediff( max(CCP_STATUS[CCP Finish Time]) - min(CCP_STATUS[CCP Start Time]), minute))

         

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, HelenaX 

    Please try the below for a measure.

     

    CCP Duration measure =
    DATEDIFF (
    SELECTEDVALUE ( CCP_STATUS[CCP Start Time] ),
    SELECTEDVALUE ( CCP_STATUS[CCP Finish Time] ),
    MINUTE
    )

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: https://www.linkedin.com/in/jihwankim1975/