Can Powerpivot transform this data

[bertusavius]

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

	E	F	G	H
Source Table
John
Frank

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #366092, align: right"]name[/TD]
[TD="bgcolor: #366092"]date[/TD]
[TD="bgcolor: #366092"]start-time[/TD]
[TD="bgcolor: #366092"]stop-time[/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]1-1-2012[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]15:45[/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]15-8-2012[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]10:30[/TD]

</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
1500-1600

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]28[/TD]
[TD="align: right"]Pivot Table[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #366092, align: right"][/TD]
[TD="bgcolor: #366092"]John[/TD]
[TD="bgcolor: #366092"]Frank[/TD]
[TD="bgcolor: #366092"]James[/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #95B3D7, align: right"]1-1-2012[/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1300-1400[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]1400-1500[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]0:45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #95B3D7, align: right"]15-8-2012[/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]0800-0900[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]0900-1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]1000-1100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"][/TD]

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