Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
v-yuta-msft
Community Support
Community 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

捕获.PNG 

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community 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

捕获.PNG 

 

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.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.