Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time.

The task is to create a simple three column dataset showing all unique Machine names in the first column, Last day on which the machine worked in the second column and hours worked on the last day in the third column.

This problem can be solved by using formulas (Refer first worksheet of the workbook) but if one has to use a Pivot Table, then there would be a few problems.

1. The Grand Total for the Date Field should be blank because on cannot determine the Last day on which the machine worked across different machine types.  A conventional Pivot Table shows the Maximum of all dates appearing in the Date Field.

2. The Grand Total for the Hours worked Field should be a summation of the total hours worked on last day across all machine types.  A conventional Pivot Table shows the Maximum of all hours worked appearing in the Hours worked Field.

3. The biggest problem of them all is that there is no way to give a criteria as the Last day for that machine for computing another Field in the Pivot Table.  Please refer the file for a better understanding.

This problem can be solved using the PowerPivot.  You may refer to my solution in this workbook.

## RSS 8 Comments…

Share your views
1. 100tsky

Thank you!

very interesting and really from life

2. 100tsky

Hi!

may I suggest

Last Day Worked=
IF (
ISFILTERED ('Table1'[Equipment] );
CALCULATE (
MAX ('Table1'[Date] );
FILTER (
ALLEXCEPT ('Table1'; 'Table1'[Equipment] );
'Table1'[Hours Worked] > 0
)
)
)

and

Total hours worked on last day=
CALCULATE (
SUM ( 'Table1'[Hours Worked] );
FILTER ('Table1'; 'Table1'[Date] = [Last Day Worked] )
)

• Hi,

Thank you for your reply. While the result of 'last day worked' seems to work fine, I believe the result for 'Total hours worked on last day' is incorrect. The result as per your formula is 38,10 and 171. This is the total hours worked by each machine. We want to compute the hours worked on the 'last day worked' - the result should be 2,10 and 20.5

3. 100tsky

Thank you!

may I send you a workbook?

• If the question relates to the topic blogged about in this post, then please describe your question and share the workbook.

4. 100tsky

It's not a big deal, but I think the 2nd measure works fine in this file
https://onedrive.live.com/redir?resid=E5CFD0372E71A180%21144

• Hi,

You are absolutely correct. I was committing a mistake yesterday. Your formula returns the same result as mine.