Forum Discussion
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
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
v-yuta-msftCommunity Support
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
- 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.- v-yuta-msft
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