Forum Discussion

Dragos's avatar
Dragos
Frequent Visitor
8 years ago
Solved

Time calculation

Hi Expert
I'm new to power bi and I search for a solution to sum between time:
Column 1 I have name of seller.
Column 2 I have time from 00:00:00 to 23:59:59
Column 3 I have prices .
What I want to achieve is the sum of column 3 between 12:30:00 and 12:30:59 / between 12:31:00and 12:31:59/ 12:32:00 and 12:32:59.
Thank you and Regards
Dragos
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI Dragos

     

    Had a look at this tonight (finally).  The issue was with the [Time] column in your Sheet1 table.  It still contained milliseconds, so I added a new column [Time2] to Sheet1 that only has hours/mins/seconds.

     

    I also converted the TimeKey column in the new table to be time rather than text.

     

    I have attached the PBIX file to this message.

13 Replies

  • KeithChu's avatar
    KeithChu
    Helper I
    8 years ago

    Can you use binning to group the hour? In your case, create new group with 1 hour as the size of bin for the datetime field. 

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      Hi KeithChu

       

      You can, just add a column to the time table that strips the minutes and seconds. 

      • Dragos's avatar
        Dragos
        Frequent Visitor
        8 years ago

        Hi Phil

         

        This really solved my problem .

        I can't thank you enough.

        I already change it also in my .pbix and i fully understood the problem also.

         

        Thank you and Regards,

        Dragos

         

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Dragos

     

    If you create a Time table using the following calculated table code, you can create a relationship between the [TimeKey] column in this table with the [time] column in your main table.  Then you can use the [Minute Group] field from the new table on a visual along with the [price] column in the values (as a  SUM).  What datatype is your [time] column?

     

    Time Table = 
VAR Hours = SELECTCOLUMNS(GENERATESERIES(0,23),"Hour",format([Value],"0#"))
VAR Minutes = SELECTCOLUMNS(GENERATESERIES(0,59),"Mintutes",format([Value],"0#"))
VAR Seconds = SELECTCOLUMNS(GENERATESERIES(0,59),"Seconds",format([Value],"0#"))
RETURN SELECTCOLUMNS(
    CROSSJOIN(Hours,Minutes,Seconds),
    "TimeKey" , [Hour] & ":" & [Mintutes] & ":" & [Seconds],
    "Minute Group" , [Hour] & ":" & [Mintutes] & ":00"
    )
    • Dragos's avatar
      Dragos
      Frequent Visitor
      8 years ago

      Hi Phil 

       

      Thank you for your fast reply .

      The format is date and time .

      Created another column only with time as attached.

       

      BR

      Dragos

       

    • Dragos's avatar
      Dragos
      Frequent Visitor
      8 years ago

      The time table works perfectly.

       

      Now i don't really understand how can i bring  individualy the  sum per minute and not for all sales .

       

      Regards,

      Dragos

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi Dragos

         

        The reason it is blank is because there are no matching rows through the relationship.

         

        Are all your values in your Execution Time field the same day?  or do they vary?