Complicated count over time

[Jriker1]

I am trying to create a chart in excel and using powerpivot. Version is 2010. I would like to identify the adoption of applications in my company over time and have the data I need just not cleanly.


For reference relevant fields in powerpivot I'm using are:


Table1: Applications
Columns: application name, country, users in country, date deployed, quarter, year, quarter-year


Table2: Countries
Columns: country, users in country


I understand table2 is duped in ways but may be useful. The data is like


Applications table:
App1, Russia, 50, 12/20/2012,Q4, 2012, Q42012
App2, Russia, 50, 01/15/2012,Q1,2012,Q12012
App3, Russia, 50, 03/13/2012,Q1,2012,Q12012
App1, Canada,40,01/13/2012,Q1,2012,Q12012
App2, China,300,01/16/2013,Q1, 2013, Q12013


Countries table:
Russia,50
Canada,40
China,300


So the applications table is repetitive of countries and user counts. It also has a list of dates the country first used the app. The county table just says for this country here is the number of users.


Application and countries table linked by country name


I want a report that filters in when a country first came on board using the applications and total count. Not country specific just totals per quarter. In the above case the chart would show graphically of course:


Q12012: 90
Q42012: 90
Q12013: 390


So I only care about the oldest date a country started using the applications and calculate total adoption over time. So as you see the first one listed is 90 just one Russia and one Canada. Then Q4 even though Russia adopted more they are already users so not new users. Then in Q1 2013 China came on board so total adoption users was now 300.


Any idea how to do this? Open to provide any clarification necessary.


Thanks.

 

[miguel.escobar]

Are you using Powerpivotv1 (the one released in 2010) ? if not, and you're using V2 then you could use FIRSDATE The Powerpivot Way - Find Earliest Date From List Of Dates Given 2 Criteria - YouTube