Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Average based on the time and date

    Posted 15 days ago
    Edited by Ab Ab 15 days ago

    Hello,

    I have the following columns Date&Time, Time1 and Time2 in SQL direct query, those three columns date type is date & time, time.

    I created measure based on the Time1 and Time2 measures for time difference by using this DAX Time Difference =

    SELECTEDVALUE('TIME'[Time1])-SELECTEDVALUE('TIME'[Time2]) and date type in time format.

    I am trying to get average time difference by Time1 and Date&Time column based on the time difference measure. 

    Also, I would like to populate the measure column into stacked column chart in Y axis, Time1 in X axis and Date&Time column in slicer.

    Please note: The source of data in direct query method so I am unable to use any power query and custom columns option because direct query not supported. 

    I am looking for measure formula for average.

    Can you please assist me.

    Data:

    Date&TimeTime1Time2Time Difference

    12/12/2022 18:21:25 18:21:25 20:47:44 02:26:19
    12/12/2022 18:22:08 18:22:08 20:44:49 02:22:41
    12/12/2022 18:22:08 18:22:08 20:44:49 02:22:41
    08/12/2022 23:08:22 23:08:22 09:05:39 14:02:43
    08/12/2022 18:21:25 18:21:25 20:47:44 02:26:19
    06/12/2022 05:38:08 05:38:08 09:05:37 03:27:29
    05/12/2022 05:36:39 05:36:39 16:56:45 11:20:06
    04/12/2022 10:29:38 10:29:38 20:28:56 09:59:18
    03/12/2022 10:29:49 10:29:49 20:39:30 10:09:41
    02/12/2022 05:27:28 05:27:28 16:29:52 11:02:24
    01/12/2022 10:31:24 10:31:24  
    01/12/2022 10:31:24 20:38:56  
    01/12/2022 10:31:24 14:06:08  
    01/12/2022 10:31:24 15:30:33  
    01/12/2022 10:31:24 22:59:00  

    Desired output in Excel.


    Here is the link of the PBI file.

    https://www.dropbox.com/s/b581j3xoikpjgtj/Time%20Difference-18-01-2023.pbix?dl=0
    ------------------------------
    Ab Ab
    Engineering
    ------------------------------



  • 2.  RE: Average based on the time and date

    Posted 12 days ago
    Hi Ab Ab,

    I just want point, that logic you used in this example is crap.
    You CAN NOT use just Time to calculate Time range if start and endare in different dates.
    Look on row from your example:
    If something started 8/12/2022 at 23:08 and ended at 09:05 (a assume 9/12) it took 09:05 + 00:52 = 9 hrs 57 mins, NOT 14:02, as is in your calculation.

    And to resolve your problem you need additional colum with date only. And use this column as a filter to calculate average.

    Regards,


    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------

    Original Message


  • 3.  RE: Average based on the time and date

    Posted 12 days ago
    Edited by Ab Ab 12 days ago

    Hi,

    Thanks for your reply and point out the mistake. Now, I am included the two date, date1 and date2. 

    Could you please help me how can I get the time difference in-between two times and average depends on the time difference in each row by date?

    I am looking for measure to get the result.

    Here is the data 

    Date1 Date2 Time1 Time2
    12/12/2022 12/12/2022 18:21:25 20:47:44
    12/12/2022 12/12/2022 18:22:08 20:44:49
    12/12/2022 12/12/2022 18:22:08 20:44:49
    08/12/2022 09/12/2022 23:08:22 09:05:39
    08/12/2022 08/12/2022 18:21:25 20:47:44
    06/12/2022 06/12/2022 05:38:08 09:05:37
    05/12/2022 05/12/2022 05:36:39 16:56:45
    04/12/2022 04/12/2022 10:29:38 20:28:56
    03/12/2022 03/12/2022 10:29:49 20:39:30
    02/12/2022 02/12/2022 05:27:28 16:29:52
    01/12/2022 01/12/2022 10:31:24
    01/12/2022 01/12/2022 20:38:56
    01/12/2022 01/12/2022 14:06:08
    01/12/2022 01/12/2022 15:30:33
    01/12/2022 01/12/2022 22:59:00



    ------------------------------
    Ab Ab
    Engineering
    ------------------------------

    Original Message