Get MIN date across related tables?

[pete234]

I am trying to get MIN (First) date but across several related tables. The tables are related to a fiscal table and a unqiue acct table. I want to know the MIN created date in a pivot but it isn't understood and keeps listing seperately. I can mix the tables for other records but just need to attach a ranking to this date field and put in next to COUNT fields. If I can calculate in the pivot table that would be ok, but I need to treat it like one field.

 

[powerpivotpro]

How about writing three measures - one that calculates the min date in each table.

Then writing a fourth measure that is a branching IF, that either returns [M1], [M2], or [M3] after comparing the values of those measures against each other.

Does that make sense?

 

[pete234]

I will look at this at some point but due to me being the only user of power pivot, small chance of sharepoint integration etc. and no one trusting nice clean data as opposed to workbooks full of formulae, it looks like power pivot is going to be on the backburner. I think I did a lot in it, matching up various reports by date and rep, getting totals that used to take 9 seperate pivots all in the same table all around one key table. So back to the icky grind of regular Excel for me.

 

[powerpivotpro]

Pete, can you tell me a bit more about the lack of trust? Are you seeing people trust a powerpivot workbook less than a "normal" one?

 

[pete234]

Well I should clarify that they are used to building out formula laden workbooks with a lot of redundant vlookups whereas I'm more used to Access. I think the distrust extends to anything they can't see calculating and isn't immediately modifiable by adding another formula. The problem for me is, I find it difficult to trust or decipher a report that has 1400 formula on the first page. I'd rather be adding meaningful analysis easily than figure out why a reference broke and the calculations are off. I decided not to give up quite yet and this may depend on how easily it is to transfer my workbook to another user and rebuild refresh links, should they actually install it. Haven't checked that part yet.