Summarize min date in data model pivot

howesmr

New Member
Joined
Aug 10, 2018
Messages
3
Hello,

I have a need in a pivot summarize one column by min and max date and another column by distinct count. In a default pivot the min and max date work fine but I cant get distinct count. So when I create the pivot in data model mode the distinct count works fine but now I can summarize my min and max date. It says "We Cant summarize this field with Min because its not a supported calculation for Date data types.

So I have had to create two separate pivot tables and use a look up formula next to the results of one pivot to bring in the distinct count from the other. My file was already huge and bogged down editing but with the addition distinct count pivot the size is far bigger and slower.

So I either need a way to get distinct count as an additional column in my data based on two criteria so that way the normal pivot will work and i can just use average or min as all the distinct counts will be the same when these two criteria are the same or get min date in a data model pivot.

Thanks in advance
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,136,435
Messages
5,675,850
Members
419,587
Latest member
leronardo092004

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top