Create a Measure to Calculate Time Duration

[HelenaX]

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. 

[Jihwan_Kim · Accepted solution]

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/

[Jihwan_Kim · Accepted solution]

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/

[HelenaX]

@Jihwan_Kim  Yes! This works! Thank you so much for your help!!

[amitchandak]

@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]

@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]

@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))

[HelenaX]

@amitchandak Is above formula calculating the max of the whole column of "finish time" and compare it to the min of the whole column of "start time"? I actually need the "Duration" for each row, just like the column called the "CCP Duration" (but as a measure). There are many more rows in this table (and for each country, there is a row each day over a period of time), and I need the duration to be calcuated for each row. Could you please help further?

[HelenaX · Accepted solution]

@amitchandak  I got the solution from this post:

https://community.powerbi.com/t5/Desktop/Create-a-Measure-to-Calculate-Time-Duration/m-p/1799103#M70...

Still, I really appreciate your quick help! I didn't provide enough data to help you to understand my need. Thanks for your help!