cancel
Showing results for
Did you mean:
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:

 Name Start End Minutes Salary / Minute Total Salary Amanda 31-01-2019 10:00 31-01-2019 20:00 600 1,67 1.000 Eduardo 31-01-2019 12:00 31-01-2019 22:47 647 1,67 1.078 Clara 31-01-2019 12:30 31-01-2019 20:15 465 2,00 930 Gabriel 31-01-2019 16:00 31-01-2019 22:46 406 1,67 677 Sofia 31-01-2019 17:00 31-01-2019 22:38 338 1,83 620 Jane 31-01-2019 18:00 31-01-2019 22:33 333 1,67 555 John 31-01-2019 18:00 31-01-2019 22:32 272 2,00 544

and the desired output looks like this:

 Time Salary 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

## 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: | |
Frequent Visitor

## Re: Change salary cost from time intervals to minuts

Thank you very much, it worked

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

## 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: | |
Frequent Visitor

## Re: Change salary cost from time intervals to minuts

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?

Frequent Visitor

## Re: Change salary cost from time intervals to minuts

Is it possible to get further assistance in this case?

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: | |