cmcreynolds
Active Member
- Joined
- May 21, 2015
- Messages
- 295
Hello everyone -
I have one table with member IDs and when they joined our organization (We'll call it TableA) and another table with IDs and when they were marked "inactive" for not paying dues (TableB). My final objective is to calculate the number of years (or months...that part is flexible at this point) they have been in the organization.
In Power Query, I merged TableA with TableB using a LeftJoin (perhaps it should have been a different join?) and got the following (sorry about the formatting of the table:
ID In Out
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Question 1)
Question 2)
I'd appreciate any suggestions on the join or an if statement.
cmcreynolds
I have one table with member IDs and when they joined our organization (We'll call it TableA) and another table with IDs and when they were marked "inactive" for not paying dues (TableB). My final objective is to calculate the number of years (or months...that part is flexible at this point) they have been in the organization.
In Power Query, I merged TableA with TableB using a LeftJoin (perhaps it should have been a different join?) and got the following (sorry about the formatting of the table:
ID In Out
0001 | 3/7/2001 | 5/14/2004 |
0001 | 3/7/2001 | 4/17/2006 |
0001 | 3/7/2001 | 4/1/2013 |
0001 | 6/25/2004 | 5/14/2004 |
0001 | 6/25/2004 | 4/17/2006 |
0001 | 6/25/2004 | 4/1/2013 |
0001 | 6/8/2006 | 5/14/2004 |
0001 | 6/8/2006 | 4/17/2006 |
0001 | 6/8/2006 | 4/1/2013 |
0001 | 9/16/2010 | 5/14/2004 |
0001 | 9/16/2010 | 4/17/2006 |
0001 | 9/16/2010 | 4/1/2013 |
0002 | 10/26/2005 | |
0003 | 10/22/2003 | |
0003 | 12/6/2008 | |
0004 | 3/7/2005 | 4/1/2015 |
0004 | 4/13/2015 | 4/1/2015 |
0005 | 3/28/2002 | |
0006 | 1/9/2003 | 3/30/2007 |
0006 | 1/9/2003 | 4/1/2008 |
0006 | 4/12/2007 | 3/30/2007 |
0006 | 4/12/2007 | 4/1/2008 |
0006 | 12/1/2009 | 3/30/2007 |
0006 | 12/1/2009 | 4/1/2008 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Question 1)
Looking at member 0006 - they came in in 2003, left in 3/2007 came back in 4/2007, left in 4/2008 and came back in 12/2009 and is still here (presumably). (there are other members more disparate than this one, so, I know there's only one month in one of the gaps). So, I need:
1/9/2003 - 3/30/2007 = 50 months
4/12/2007 - 4/1/2008 = 12 months
12/1/2009 - (today) = 83 months
for a total of approximately 145 months for 0006.
1/9/2003 - 3/30/2007 = 50 months
4/12/2007 - 4/1/2008 = 12 months
12/1/2009 - (today) = 83 months
for a total of approximately 145 months for 0006.
Question 2)
Now, of course things are not as nice and neat as they should be - look at 0003, they came in twice without being excluded (who knows why). In that case, I'd like to calculate 10/22/2003 - today.
I'd appreciate any suggestions on the join or an if statement.
cmcreynolds