Count hours between Slicer dates

[EmaVasileva]

Hi,

I need help with calculating the task completion in hours, between slicer dates. I have the following data:

Category	Task	StartDate&Time	EndDate&Time	Full Duration in Hours
A	A.1	2/14/24 9:30 PM	2/22/24 7:30 PM	190
B	B.1	2/14/24 8:00 AM	2/14/24 9:30 AM	1.5
B	B.2	2/14/24 10:00 PM	2/16/24 6:00 AM	32

For ex.: Cat A, Task A.1 starts on Feb-14-24 9:30 PM and ends on Feb-22-24 7:30 PM, so its duration was 190 h (every day counts as 24 hours. If I select from the date slicer (for which I’m using StartDate and EndDate from the table above) Feb 15th, I need the duration hours to show the duration between Feb 15th and 22 Feb, so the number should be 187.5 hours

If I select from the slicer StartDate = Feb 15th 24 and EndDate Feb 21st, I need to see a duration= 168 h.

I would like to see the real startdate and Enddate for the task, even the slicer is for another period, but I would like to receive the actual duration based on the slicer. Please let me know if it's necessary to use another field as a Slicer and how to calculate the duration. Thank you.

[v-zhouwen-msft]

Hi @EmaVasileva ,

The table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure named ‘Full Duration in Hours’

Full Duration in Hours = DIVIDE(DATEDIFF(SUM('Tabelle1'[StartDate&Time]),SUM('Tabelle1'[EndDate&Time]),MINUTE),60) & "h"

2. If you want to get the actual hourly difference between the two slicers
2.1Use the following DAX expression to create a table named ‘Year-round dates’

Year-round dates = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

 2.2Use the following DAX expression to create a measure named ‘Slicer_Duration’

Slicer_Duration = DATEDIFF(MIN('Year-round dates'[Date]),MAX('Year-round dates'[Date]),HOUR)

3. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EmaVasileva]

Hi @v-zhouwen-msft ,

Thank you for the response, but it's not quite the result I'm looking for. I need to get the sum of the hour duration between Slicer start date and actual Startd_Date_Time, and the sum between the Slicer end date and the actual End-Date_Time in the table. 

Because in some cases we can have the actual Start_Date_Time to be after 12am (but in the slicer we have only date and 12:00am by default) and to be finished a day earlier that the end date in the slicer.

Example 1:
Slicer dates = 14 Feb 2024 12:00 Am - 22 Feb 2024 12:00AM
Start&End date in the table: Feb 14 2024 09:30 PM  - Feb 22 2024 07:30 PM

If i take only the duration from the slicer I would receive 192 h, in this case it will be the right one. 

Example 2: Slicer dates = 15 Feb 2024 12:00 Am - 22 Feb 2024 10:00PM
Start&End date in the table: Feb 14 2024 09:30 PM  - Feb 22 2024 07:30 PM

If i take only the duration from the slicer I would receive 144 h, but the needed and the right result is 187.5h. (from 15 Feb 2024 12:00 Am to 22 Feb 2024 07:30PM).

If the start date in the slicer is sooner than the one in the table, we took the one in the table. If the end date in the slicer is sooner than the end date in the table, we took the one in the slicer, but if the end date is later than the one in the table, we took the one in the table.