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]

What you get with the formula is the time expressed in hours (1,75 = 01:45) broken down by hour range, as already done by bertusavius.

If you have days on rows and names on columns, then you should indeed get the total hours worked per day & user.

 

[JavierGuillen]

Laurent,

I tested your expression and I like it a lot. Great solution using CROSSJOIN to set the granularity of the calculation.

I also tested your original formula as well as the one using GENERATE on 20,000 records and I got the result back (in a pivot table) in aprox 10 seconds.

The dataset was 100 employees for 200 days.

bertusavius, could that be a realistic representation of your data set? Or is it structured in a different way?

Also, I removed the multiplication (* 24), as you won't need it when setting the format of the measure to Date and format string H:mm

 

[bertusavius]

Guys, first of all I should mention that the performance issue was the result of another measure I used to expand the model. I'm really sorry I didnt mention this earlier.
I used this measure to pull data from a table with cost information. (actually wages)

SUMX(
CROSSJOIN( CROSSJOIN('Tabel13';'Tijd') ; 'Tarieven' );
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd])
; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd])
-
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
;0) * (24) * if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag] ; 0)
)
-
SUMX( CROSSJOIN( CROSSJOIN('Tabel13';'Tijd') ; 'Tarieven' ) ;
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd])
; ((Tabel13[Pauze]/60) / ('Tabel13'[Stoptijd] - 'Tabel13'[Starttijd] ))
*
(
if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd])
-
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
)
* if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag];0)
; 0))

It seems that this "double crossjoin" is a bit too much, even with recent hardware.
For now I'm using an average wage as alternative, although i'm still open to suggestions regarding code that works faster.




More important secondly:
I discovered that as soon as shifts run after midnight, the calculation doesn't work.
It doesnt seem to calculate anything at all, even the hours before midnight.
This is quite a problem because a substantial amount of shifts get affected by this.

 

[Laurent C]

1. Note that you may have more than 2 tables in a cross join;
if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag];0)
This part of the expression indicates you have a natural relationship between your Tarieven table and Table13. Use it in your model, then you will not need any cross-join and can use RELATED('Tarieven'[Bedrag]).

Point 2: you should take the date into account within your calculation, otherwise, it will indeed not work.

 

[bertusavius]

1) You are absolutely right about the fact that there is a relationship between Tarieven table and Table13. It was also my first approach to simply use RELATED('Tarieven'[Bedrag])
However the PT 'imploded' every time I tried.

2)The priority atm lies at this problem. My time data should be correct before I connect it to cost date.
Here I have an example of a shift that symbolises all the challenges in one. Notice the shift in date when the end-time exceeds 0200 hour.

[TABLE="width: 248"]
<TBODY>[TR]
[TD="class: xl68, width: 82, bgcolor: black"]from:
[/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]ID
[/TD]
[TD="class: xl65, bgcolor: transparent"]datum
[/TD]
[TD="class: xl65, bgcolor: transparent"]start
[/TD]
[TD="class: xl65, bgcolor: transparent"]stop
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]21:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2:45
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: black"]to:
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]ID
[/TD]
[TD="class: xl65, bgcolor: transparent"]datum
[/TD]
[TD="class: xl65, bgcolor: transparent"]bucket
[/TD]
[TD="class: xl65, bgcolor: transparent"]value
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]etc
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2000-2100
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2100-2200
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,5
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2200-2300
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2300-2400
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2400-2500
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]2500-2600
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]0200-0300
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,75
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9999
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2-1-2012
[/TD]
[TD="class: xl65, bgcolor: transparent"]0300-0400
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]etc
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


The latest attempt at code doenst give a right answer too; shifts that start and stop before midnight are calculated wrongly now.

SUMX(
CROSSJOIN('Tabel13';'Tijd');
IF(AND(
IF(AND(Tabel13[Starttijd]>=if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd]);
Tabel13[Stoptijd] >Tijd[Start tijd]
)
;
Tijd[Start tijd]
;
Tabel13[Starttijd]
) < if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
;
IF(AND(Tabel13[Stoptijd] <=Tijd[Start tijd];
Tabel13[Starttijd]<IF(TIJD[START tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
)
;
if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
;
Tabel13[Stoptijd]
) > Tijd[Start tijd]
)
;
IF(AND(Tabel13[Stoptijd] <=Tijd[Start tijd];
Tabel13[Starttijd]<IF(TIJD[START tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
)
;
if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
;
if(Tabel13[Stoptijd]>Tijd[Eind tijd];Tijd[Eind tijd];Tabel13[Stoptijd])
)
-
IF(AND(Tabel13[Starttijd]>=if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd]);
Tabel13[Stoptijd] >Tijd[Start tijd]
)
;
Tijd[Start tijd]
;
if(Tabel13[Starttijd]>Tijd[Start tijd];Tabel13[Starttijd];Tijd[Start tijd])
)
;
0
) * 24
)

 

[Laurent C]

From your results, the measure is ok.

The only problem I see is with the date itself, which actually comes from the data.

Anything fancy about the "Datum" field in your pivot table?

 

[bertusavius]

Oh, the above tables were examples.
They were meant to illustrate the desired result.

The real output is certainly not correct.
If you are interested, I could send you the file of course. It would surely make it less abstract.

The datum field is always a date.

Looking back, I should have used English names instead of Dutch ones.

 

[Laurent C]

If you post a link to your table (Skydrive or whatever ...) I will take a look at it.

Why do you want to have this shift date, if the end time is past 02:00 ?

More specifically, how would you define the meaning of the "Datum" field?

In any case, if you want to obtain this result, I recommend either importing your data with the date as you want it to be defined, or create a calculated column and adjust the relationship between your table and your date dimension, if you have one.

 

[bertusavius]

It is correct that I need the date to change after two o clock.
The meaning of the datum field is the start date of the shift.
I want to be able to say that a certain shift runs into another working day.
The situation i'm working in regrettetly defines that another working dat starts at two o clock in the morning.

I just sent you a PM btw.

 

[bertusavius]

I think I nailed it with this code

=SUMX(
* * * * CROSSJOIN('Tabel13';'Tijd');
IF(AND(
* * * * * *IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
* * * * * *;
* * * * * *IF(Tabel13[Stoptijd]<=(1/24*4);Tabel13[Stoptijd]+1;Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * )
* * * * *;
* * * * * if(IF(Tabel13[Stoptijd]<=(1/24*4);Tabel13[Stoptijd]+1;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);Tabel13[Stoptijd]+1;Tabel13[Stoptijd])
* * * * * * )
* * * * * *-
* * * * * *IF(IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * * * *;
* * * * * * * *IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd])
* * * * * * * *;
* * * * * * * *IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * * *)
* * * * *;
* * * * *0
* * * ) * 24)