Change salary cost from time intervals to minuts

[ThomasBachmann]

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?

[v-shex-msft]

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

[ThomasBachmann]

@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?

[v-shex-msft]

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

[ThomasBachmann]

@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]

@v-shex-msft 

Is it possible to get further assistance in this case?

[v-shex-msft]

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

[ThomasBachmann]

Thank you very much, it worked