Can Powerpivot transform this data

[bertusavius]

I have a source table that contains time information about shifts people work:

	E	F	G	H
37	Source Table
38	name	date	start-time	stop-time
39	John	1-1-2012	13:00	15:45
40	Frank	15-8-2012	8:45	10:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Blad4​

I would like to transform this information into a pivot table using PowerPivot in order to get the following result:

	N	O	P	Q
28	Pivot Table
29		John	Frank	James
30	1-1-2012
31	1300-1400	1:00
32	1400-1500	1:00
33	1500-1600	0:45
34	15-8-2012
35	0800-0900		0:15
36	0900-1000		1:00
37	1000-1100		0:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Blad4​

What would be a good way to approach this problem?

 

[Laurent C]

Have you checked that the dates are correct?

After seeing your model, I suggest you add a date dimension to your model (with year, month, day name, ...) instead of adding them as separate tables.

You might as well add a calculated column directly in the model, for the end date.

Also, in the file you sent, the very last bucket was not correct (23:00 to 00:00 instead of 23:00 to 24:00), resulting in lost data.

 

[bertusavius]

Just here to let you know that the problem is sufficiently solved.

In the end I also needed to subtract some time for each shift (pauze).
I also added a column that defines the date for buckets of shifts that run past midnight.


=SUMX(
CROSSJOIN('Tabel13';'Tijd');

(if(or(Tabel13[Starttijd]>= ((1/24)*4);Tabel13[Starttijd] > Tabel13[Stoptijd]);
IF(AND(
IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tabel13[Stoptijd]<=(1/24*<wbr>4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
)
;
if(IF(Tabel13[Stoptijd]<=(1/<wbr>24*4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tabel13[Stoptijd]<=(1/24*4)<wbr>;Tabel13[Stoptijd]+1;Tabel13[<wbr>Stoptijd])
)
-
IF(IF(Tabel13[Starttijd]<((1/<wbr>24)*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
;
IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd])
;
IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
)
;
0
)
;
IF(AND( Tabel13[Starttijd] < Tijd[Eind tijd]
;Tabel13[Stoptijd] > Tijd[Start tijd]
)
;
if(Tabel13[Stoptijd] < Tijd[Eind tijd];Tabel13[Stoptijd];Tijd[<wbr>Eind tijd])
-
if(Tabel13[Starttijd] > Tijd[Start tijd];Tabel13[Starttijd];Tijd[<wbr>Start tijd])
;
0
)
)
-
if(or(Tabel13[Starttijd]>= ((1/24)*4);Tabel13[Starttijd] > Tabel13[Stoptijd]);
IF(AND(
IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tabel13[Stoptijd]<=(1/24*<wbr>4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
)
;
if(IF(Tabel13[Stoptijd]<=(1/<wbr>24*4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
;
IF(Tabel13[Stoptijd]<=(1/24*4)<wbr>;Tabel13[Stoptijd]+1;Tabel13[<wbr>Stoptijd])
)
-
IF(IF(Tabel13[Starttijd]<((1/<wbr>24)*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
;
IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd])
;
IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
)
;
0
)
;
IF(AND( Tabel13[Starttijd] < Tijd[Eind tijd]
;Tabel13[Stoptijd] > Tijd[Start tijd]
)
;
if(Tabel13[Stoptijd] < Tijd[Eind tijd];Tabel13[Stoptijd];Tijd[<wbr>Eind tijd])
-
if(Tabel13[Starttijd] > Tijd[Start tijd];Tabel13[Starttijd];Tijd[<wbr>Start tijd])
;
0
)


)*(if(isblank(Tabel13[Pauze])<wbr>;0;(Tabel13[Pauze]/60) / (Tabel13[Gewerkt] + (Tabel13[Pauze]/60)))))