cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dragos Frequent Visitor
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Time calculation

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

13 REPLIES 13
Phil_Seamark Super Contributor
Super Contributor

Re: Time calculation

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dragos Frequent Visitor
Frequent Visitor

Re: Time calculation

Hi Phil 

 

Thank you for your fast reply .

The format is date and time .

Created another column only with time as attached.

 

BR

Dragos

 date&time.JPGtime.JPG

Dragos Frequent Visitor
Frequent Visitor

Re: Time calculation

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

Total.JPG

Phil_Seamark Super Contributor
Super Contributor

Re: Time calculation

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dragos Frequent Visitor
Frequent Visitor

Re: Time calculation

Hi Phil

 

The dates are different .

Every day new sales .

 

What i did i created a new column only with the time and did the relationship between mytable[time] and timetable[minute group].

And then to filter by day .

 

I thought this way will match.

 

Thank you and Regards,

Dragos 

 

Phil_Seamark Super Contributor
Super Contributor

Re: Time calculation

Hi @Dragos

 

Good idea creating a new column with just the time.  Do they match now?  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dragos Frequent Visitor
Frequent Visitor

Re: Time calculation

Hi Phil

 

Unfortunatly no .

Seems that it brings me only the total.

 

Best,

Dragos

Phil_Seamark Super Contributor
Super Contributor

Re: Time calculation

The values in columns on both sides of a relationship need to be the same.

 

Any chance you can share your PBIX file?  Delete all tables apart from these two.  Delete every column from your main table apart from the bare minimum and share.  I let you know what to do then Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dragos Frequent Visitor
Frequent Visitor

Re: Time calculation

Hi Phil

 

Much appreciated.

 

Please find bellow the .pbix file.

 

 

Thank you and Regards,

Dragos

https://drive.google.com/open?id=1MflNztz9YX5dTsD3WORhslbxMoyW9Ays