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

Date time filter,throughput,percentile

Hello All,

 

Back again.Smiley Happy

 

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

捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post


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

捕获.PNG 

 

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.

 

捕获.PNG 

 

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..