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

Change salary cost from time intervals to minuts

I am trying to measure our salary cost by 15 min intervals.

Now we are only getting it by the day.

 

My current data looks like this:

NameStartEndMinutesSalary / MinuteTotal Salary
Amanda31-01-2019 10:0031-01-2019 20:00600                        1,67                          1.000    
Eduardo31-01-2019 12:0031-01-2019 22:47647                        1,67                          1.078    
Clara31-01-2019 12:3031-01-2019 20:15465                        2,00                             930    
Gabriel31-01-2019 16:0031-01-2019 22:46406                        1,67                             677    
Sofia31-01-2019 17:0031-01-2019 22:38338                        1,83                             620    
Jane31-01-2019 18:0031-01-2019 22:33333                        1,67                             555    
John31-01-2019 18:0031-01-2019 22:32272                        2,00                             544    

 

and the desired output looks like this:

TimeSalary
17:00                          132,50    
17:15                          132,50    
17:30                          132,50    
17:45                          132,50    
18:00                          187,50    
18:15                          187,50    
18:30                          187,50    
18:45                          187,50    

 

Can anybody help with accomplishing this?

7 REPLIES 7
Community Support Team
Community Support Team

Re: Change salary cost from time intervals to minuts

HI @ThomasBachmann,

 

I'd like to suggest you create time interval table and bridge table with with name and time range, then use name field to link original table, time range to link time interval table.

 

 

Time interval Table =
SELECTCOLUMNS (
    GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 15, 0 ) ),
    "Time", [Value]
)

Bridge =
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( Table, 'Time Interval' ),
[Time] >= TIMEVALUE ( [Start] )
&& [Time] <= TIMEVALUE ( [End] )
),
"Name", Table[Name],
"Time Range", [Time]
)

After these, you can simply create graph with time interval table and original table fields. 

 

Notice: It will generate huge amount of records with each time interval range defined by each row start end time.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
ThomasBachmann Frequent Visitor
Frequent Visitor

Re: Change salary cost from time intervals to minuts

Thank you very much, it worked

ThomasBachmann Frequent Visitor
Frequent Visitor

Re: Change salary cost from time intervals to minuts

@v-shex-msft

 

The solution does not work when we have a shift that stretches across two days.

Ex.

 

Start

15-02-2019 17:00:00 

 

End

16-02-2019 01:00:00

 

How to resolve this issue?

Highlighted
Community Support Team
Community Support Team

Re: Change salary cost from time intervals to minuts

Hi @ThomasBachmann,

 

You can cross join date part with your records before expand time table, then add condition to check both date and time values.

 

BTW, I'm not so suggest you to expand date and time at same time.(it will generate huge amount of records)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
ThomasBachmann Frequent Visitor
Frequent Visitor

Re: Change salary cost from time intervals to minuts

@v-shex-msft 

 

I have now created two crossjoined tables.

 

Workplan 1 =
FILTER (
CROSSJOIN ( Workplan; 'Date Table' );
'Date Table'[Dates] >= Workplan[Date.Start]
&& 'Date Table'[Dates] <= Workplan[Date.End]
)

 

--

 

Workplan 2 =
FILTER (
CROSSJOIN ( 'Workplan 1'; 'Time Table' );
'Time Table'[Time] >= TIMEVALUE('Workplan 1'[Start])
&& 'Time Table'[Time] <= TIMEVALUE('Workplan 1'[End])
)

 

However it is still not giving me shifts that cross two dates?

ThomasBachmann Frequent Visitor
Frequent Visitor

Re: Change salary cost from time intervals to minuts

@v-shex-msft 

 

Is it possible to get further assistance in this case?

Community Support Team
Community Support Team

Re: Change salary cost from time intervals to minuts

Hi @ThomasBachmann ,

 

If you expand both date and time part, bridge table will generate too many records.(it will effect performance)

 

In my opinion, I'd like to suggest you extract two table with date and time part, then you can build visuals through date/time dimensions.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |