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 ...
  • 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.

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"
    )