cancel
Showing results for
Did you mean:
Dain Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

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

4 REPLIES 4 Community Support Team

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

Dain Frequent Visitor

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

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

Dain Frequent Visitor

Re: Date time filter,throughput,percentile

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

Announcements Top Kudoed Authors
Users Online
Currently online: 294 members 3,463 guests
Recent signins:
• copykai • sneds • ToddChitt • admin_xlsior • StianHog • MattAnnandale • joegrassby • blhart216 • mss_powerbi • GWTF • antonsundh • Marko_OK • payalmehakare • Abiyadav 