SOLVED
Home

Pivot Table Values not matching Source values (Max Date)

%3CLINGO-SUB%20id%3D%22lingo-sub-862184%22%20slang%3D%22en-US%22%3EPivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862184%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20pivot%20table%20is%20changing%20the%20value%20of%20the%20source%20data%20to%20a%20different%20number%2C%20even%20though%20the%20cells%20are%20formatted%20exactly%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20containing%20dates%20of%20calls%20with%20timestamps%20for%20individual%20cases%20with%20unique%20ID's.%26nbsp%3BI%20have%20formatted%20the%20date%2Ftime%20data%20to%20Number%20values%2C%20with%2010%20decimal%20places%20to%20get%20the%20full%20number%20without%20rounding.%26nbsp%3B%20I%20created%20a%20pivot%20table%20pulling%20the%20max%20date%20value%20for%20each%20ID%20from%20these%20fields.%20It's%20important%20that%20the%20number%20result%20include%20the%20timestamp%2C%20because%20there%20are%20some%20ID's%20with%20multiple%20calls%20on%20the%20same%20day%2C%20and%20I%20need%20the%20very%20latest%20call.%20The%20results%20of%20the%20max%20call%20date%20(number)%20in%20the%20pivot%20table%20for%20the%20exact%20call%20in%20the%20table%20from%20which%20the%20data%20is%20being%20pulled%20is%20a%20different%20number.%20See%20below%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESource%20info%20value%3A%207%2F3%2F18%204%3A48%20PM%20(m%2Fd%2Fyy%2012%3A00%20am)%3C%2FP%3E%3CP%3EResult%20when%20formatted%20as%20Number%3A%26nbsp%3B43284.7004137731%3C%2FP%3E%3CP%3EResult%20when%20max%20date%20calculated%20in%20pivot%20table%3A%26nbsp%3B43284.7004166667%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20thorough%20troubleshooting%20from%20the%20formatting%20angle%2C%20so%20I%20know%20it's%20not%20a%20formatting%20selection%20issue.%20For%20example%2C%20I%20opened%20the%20data%20results%20from%20the%20pivot%20table%2C%20and%20the%20data%20is%20formatted%20as%20%22mm%2Fdd%2F7777%2024%3A00%22%20so%20I%20tried%20re-formatting%20the%20original%20data%20to%20the%208-digit%2C%20military%20time%20format%20but%20it%20did%20not%20impact%20the%20original%20number%20value%20result.%20I%20can%20confirm%20this%20because%20in%20my%20source%20data%20table%20I%20am%20concatenating%20the%20date%20value%20with%20the%20case%20ID's%20in%20a%20separate%20field%20to%20create%20their%20unique%20ID's%20to%20perform%20a%20final%20search.%20So%20for%20example%2C%20the%20above%20is%20%22ABCDEFG43284.7004137731%22%20and%20even%20after%20changing%20the%20formatting%20to%20match%20the%20results%20value%20from%20the%20pivot%20table%2C%20the%20number%20value%20did%20not%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-862184%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epivot%20table%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862728%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862728%22%20slang%3D%22en-US%22%3ECan%20you%20post%20a%20workbook%20with%20just%20the%20ID's%20and%20dates%20and%20a%20pivot%20based%20on%20those%20two%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863353%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411445%22%20target%3D%22_blank%22%3E%40dwanajw%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20it%20works%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20322px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132615i6931EE8EC7355840%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EActually%20your%20timestamp%20has%20seconds%20and%20milliseconds.%20If%20add%20data%20to%20data%20model%20creating%20Pivot%20Table%20you%20have%20correct%20result.%20If%20not%20-%20Pivot%20Table%20rounds%20milliseconds.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866939%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866939%22%20slang%3D%22en-US%22%3EHi%20Sergey%2C%20I%20didn't%20know%20that.%3CBR%20%2F%3EFunny%20enough%2C%20if%20you%20change%20the%20format%20of%20the%20source%20data%20to%20general%20and%20then%20create%20a%20normal%20pivottable%20it%20does%20get%20the%20correct%20precision.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-867207%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-867207%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jan%20Karel%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20also%20rounds%20in%20formula%20bar%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20252px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132772iE3D5465959506BCA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThat's%20only%20formatting%2C%20but%20if%20hit%20enter%20in%20formula%20bar%20milliseconds%20will%20be%20rounded.%20Perhaps%20same%20story%20with%20some%20other%20operations%2C%20don't%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869881%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869881%22%20slang%3D%22en-US%22%3EYes%20I%20know%20about%20that%20indeed%2C%20thanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877401%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20explaining%20why%20it's%20happening.%20So%20How%20can%20I%20add%20the%20millisecond%20value%20to%20the%20Pivot%20table%20calculations%3F%20OR%20remove%20the%20millisecond%20value%20from%20the%26nbsp%3B%20source%20data%20before%20creating%20the%20pivot%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877464%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411445%22%20target%3D%22_blank%22%3E%40dwanajw%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20Pivot%20Table%20add%20data%20to%20data%20model%20creating%20Pivot%20Table%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20335px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133653iF43FECB0A6E23FAD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20rounding%20source%20data%2C%20you%20may%20add%20one%20more%20column%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMROUND(%5B%40A%5D%2C%2200%3A00%3A01%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThat's%20a%20column%20C%20in%20this%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20464px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133654i7CF61C5B5CD531BE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20pivot%20your%20data%20based%20on%20this%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877954%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877954%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879709%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Values%20not%20matching%20Source%20values%20(Max%20Date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411445%22%20target%3D%22_blank%22%3E%40dwanajw%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
dwanajw
New Contributor

My pivot table is changing the value of the source data to a different number, even though the cells are formatted exactly the same.

 

I have a table containing dates of calls with timestamps for individual cases with unique ID's. I have formatted the date/time data to Number values, with 10 decimal places to get the full number without rounding.  I created a pivot table pulling the max date value for each ID from these fields. It's important that the number result include the timestamp, because there are some ID's with multiple calls on the same day, and I need the very latest call. The results of the max call date (number) in the pivot table for the exact call in the table from which the data is being pulled is a different number. See below example: 

 

Source info value: 7/3/18 4:48 PM (m/d/yy 12:00 am)

Result when formatted as Number: 43284.7004137731

Result when max date calculated in pivot table: 43284.7004166667

 

I've done thorough troubleshooting from the formatting angle, so I know it's not a formatting selection issue. For example, I opened the data results from the pivot table, and the data is formatted as "mm/dd/7777 24:00" so I tried re-formatting the original data to the 8-digit, military time format but it did not impact the original number value result. I can confirm this because in my source data table I am concatenating the date value with the case ID's in a separate field to create their unique ID's to perform a final search. So for example, the above is "ABCDEFG43284.7004137731" and even after changing the formatting to match the results value from the pivot table, the number value did not change.

 

Please help!

9 Replies
Can you post a workbook with just the ID's and dates and a pivot based on those two?
Solution

@dwanajw 

Yes, it works like this

image.png

Actually your timestamp has seconds and milliseconds. If add data to data model creating Pivot Table you have correct result. If not - Pivot Table rounds milliseconds.

Hi Sergey, I didn't know that.
Funny enough, if you change the format of the source data to general and then create a normal pivottable it does get the correct precision.

Hi Jan Karel,

 

It also rounds in formula bar

image.png

That's only formatting, but if hit enter in formula bar milliseconds will be rounded. Perhaps same story with some other operations, don't know.

Yes I know about that indeed, thanks.

@Sergei Baklan 

 

Thank you for explaining why it's happening. So How can I add the millisecond value to the Pivot table calculations? OR remove the millisecond value from the  source data before creating the pivot table?

@dwanajw 

If Pivot Table add data to data model creating Pivot Table:

image.png

If rounding source data, you may add one more column with formula

=MROUND([@A],"00:00:01")

That's a column C in this sample

image.png

and pivot your data based on this column.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies