## Desktop

Highlighted
Frequent Visitor
Posts: 14
Registered: ‎04-22-2018

# Date time filter,throughput,percentile

Hello All,

Back again.

I have a table with a column having a date time value.

Submit time

5/02/2018 19:23:09

5/02/2018 19:50:00

5/02/2018 20:50:00

5/02/2018 20:57:00

5/02/2018 20:58:10

5/02/2018 20:58:45

5/03/2018 20:50:00

5/03/2018 21:57:00

5/03/2018 21:58:10

5/03/2018 21:58:45

the values in this column will be having hourly date time value per day.

Queries:

1.I want to create a date time slicer ie.a date slicer and a hourly time slicer.

2.I want to calculate throughput of output based on the hours selected in a time slicer.ie,if the number of output is 10 for a selection 5/02/2018 19:00:00 to 5/02/2018 21:00:00 throughput = 10/2=5

5/02/2018 19:00:00 to 5/03/2018 21:00:00,throughput = 10/26=0.4

3.I want to find out the 95th percentile of total execution time.For this i have two columns processing start date/time and processing end date/time

processing start date/time   processing end date/time

5/02/2018 19:23:09               5/02/2018 19:25:09

5/02/2018 19:30:09               5/02/2018 19:37:09

5/03/2018 19:30:09                5/03/2018 19:37:10

Kindly provide your inputs for the above 3 queries.Thank you

Accepted Solutions
Community Support Team
Posts: 691
Registered: ‎02-06-2018

## Re: Date time filter,throughput,percentile

Hi Dain,

Use DAX below to create two calculate column Date and Time using column DateTime and then create two slicers based on the two calculate column.

```Date = DATE(YEAR(Table1[DateTime]), MONTH(Table1[DateTime]), DAY(Table1[DateTime]))
Time = TIME(HOUR(Table1[DateTime]), MINUTE(Table1[DateTime]), SECOND(Table1[DateTime])) ```

Then create a measure to calculate the throughput per hour using DAX like this:

```Result =
VAR Total = CALCULATE(SUM(Table1[throughput ]), ALLSELECTED(Table1))
VAR Distance = DATEDIFF(CALCULATE(MIN(Table1[Date]), ALLSELECTED(Table1)), CALCULATE(MAX(Table1[Date]), ALLSELECTED(Table1)), HOUR) + DATEDIFF(CALCULATE(MIN(Table1[Time]), ALLSELECTED(Table1)), CALCULATE(MAX(Table1[Time]), ALLSELECTED(Table1)), HOUR)
RETURN
Total / Distance```

The result is as below and PBIX here: https://www.dropbox.com/s/mhxn3259pp9yb2m/Date%20time%20filter%2Cthroughput%2Cpercentile.pbix?dl=0

Regards,

Jimmy Tao

All Replies
Community Support Team
Posts: 691
Registered: ‎02-06-2018

## Re: Date time filter,throughput,percentile

Hi Dain,

Use DAX below to create two calculate column Date and Time using column DateTime and then create two slicers based on the two calculate column.

```Date = DATE(YEAR(Table1[DateTime]), MONTH(Table1[DateTime]), DAY(Table1[DateTime]))
Time = TIME(HOUR(Table1[DateTime]), MINUTE(Table1[DateTime]), SECOND(Table1[DateTime])) ```

Then create a measure to calculate the throughput per hour using DAX like this:

```Result =
VAR Total = CALCULATE(SUM(Table1[throughput ]), ALLSELECTED(Table1))
VAR Distance = DATEDIFF(CALCULATE(MIN(Table1[Date]), ALLSELECTED(Table1)), CALCULATE(MAX(Table1[Date]), ALLSELECTED(Table1)), HOUR) + DATEDIFF(CALCULATE(MIN(Table1[Time]), ALLSELECTED(Table1)), CALCULATE(MAX(Table1[Time]), ALLSELECTED(Table1)), HOUR)
RETURN
Total / Distance```

The result is as below and PBIX here: https://www.dropbox.com/s/mhxn3259pp9yb2m/Date%20time%20filter%2Cthroughput%2Cpercentile.pbix?dl=0

Regards,

Jimmy Tao

Frequent Visitor
Posts: 14
Registered: ‎04-22-2018

## Re: Date time filter,throughput,percentile

Thank you.But I want a start and end time filter.I want to select may 1 St 9:00 am to may 2 10:00am.
Is it possible.
Community Support Team
Posts: 691
Registered: ‎02-06-2018

## Re: Date time filter,throughput,percentile

Hi Dain,

What do you mean about a start and end slicer? Do you mean user can input start date and end date in a slicer? The datetime slicer support this feature. Select "Between" option and user can input the start and end date in the two blanks.

Regards,

Jimmy Tao

Frequent Visitor
Posts: 14
Registered: ‎04-22-2018

## Re: Date time filter,throughput,percentile

No.I want to specify start date end date start time end time..