Calculate sum of duration between dates in minutes

[sudhakar111]

Hello All,

I am trying to create a formula to display the duration between dates in minutes. The requirement is to sum up all the

durations in minutes and show in a separate card visual.

The below formula works perfectly to show the duration for each row in a table visual.

Duration = DATEDIFF(Max( 'Step 2'[Visit Start] ),Max( 'Step 2'[Visit End]),MINUTE)

[amitchandak]

@sudhakar111 , Two ways

Duration =

Sumx( 'Step 2',DATEDIFF(( 'Step 2'[Visit Start] ),( 'Step 2'[Visit End]),MINUTE))

with some group by in values
Duration =

Sumx(values( 'Step 2'[ID]) ,DATEDIFF(Max( 'Step 2'[Visit Start] ),Max( 'Step 2'[Visit End]),MINUTE))

[sudhakar111]

@amitchandak  Thanks a lot. I have tried both the formula, But they doesnt seem to be working.

The first one - Sumx( 'Step 2',DATEDIFF(( 'Step 2'[Visit Start] ),( 'Step 2'[Visit End]),MINUTE))

is giving a very big number.

The Second one - Sumx(values( 'Step 2'[ID]) ,DATEDIFF(Max( 'Step 2'[Visit Start] ),Max( 'Step 2'[Visit End]),MINUTE)) 

There is no unique ID in the data for grouping. Created an index column and tried the formula, even this is giving a big number.

[sudhakar111]

@amitchandak  

The first one - Sumx( 'Step 2',DATEDIFF(( 'Step 2'[Visit Start] ),( 'Step 2'[Visit End]),MINUTE))

works fine if there are no duplicate records for visit start and visit end. The data set i have has many repeating date/time. How do i get distinct visit start and visit end with durations?

[v-kelly-msft]

Hi @sudhakar111 ,

So you wanna sum up all the duration values or just sum those distince visit start and visit end durations?

If possible,could you pls provide some sample data with expected output?

Much appreciated.

Best Regards,
Kelly

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

[sudhakar111]

@v-kelly-msft  i want to sum the distinct visit start and visit end duration.Please find the sample file on the link below. on the card visual i want to show the distinct sum of duration.

https://drive.google.com/file/d/1-4XmNctls5BI_brj3vMaHvNu194aD3BS/view?usp=sharing

[v-kelly-msft]

Hi @sudhakar111 ,

First create a column as below:

Duration_column = DATEDIFF('Visit'[Visit Start],'Visit'[Visit End],MINUTE)

Then create a measure as below:

Duration2 = 
SUMX(VALUES('Visit'[Duration_column]),'Visit'[Duration_column])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[sudhakar111]

Thanks a lot @v-kelly-msft , The sum of duration on the table and the card visual are not matching. May be a few rows are not adding up on the card visual.

[v-kelly-msft]

Hi @sudhakar111 ,

Do you mean that the result with the sample data is not correct?Do you wanna add up all the values shown below?

I've checked that the result is the total of all the distinct values.If I understand wrongly,pls correct me.

Best Regards,
Kelly

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

[sudhakar111]

@v-kelly-msft   If you look at the table visual, duration 61 minutes is repeated 3 times. I want all the 3 repeated values to be added to the sum, because their visit start/time and visit end/time are unique. I want distinct visit start and visit end dates and their durations to add up. The durations may repeat.

[v-kelly-msft · Accepted solution]

Hi @sudhakar111 ,

Sorry,I misunderstood your point.

First create a column as below:

_Combine = 'Visit'[Visit Start]&" "&'Visit'[Visit End]

Then create a measure as below:

Duration2 = 
SUMX(SUMMARIZE('Visit','Visit'[_Combine],"_Duration",MAX('Visit'[Duration_column])),[_Duration])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[sudhakar111]

Thanks a lot @v-kelly-msft. This is the result i want.