Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |