Find date difference of rows, using multiple criteria & rank in PowerPivot

[k602570]

I'd like some help on the best way to determine the days between dates in different rows, using multiple criteria. The data can have duplicates in several columns, (Names/Zip and/or Acct No) but I have already solved for that in the Max Dup column. I have already calculated rank and the start date for each instance (not sure if I need this). I have a date table and realize I will need to use Datebetween in place of Networkdays.

Rank = =if([Max_Dup]="Dup_Name_Zip",RANKX(filter(ALL(Citris),[A_CustName]=EARLIER([A_CustName])),[ClosedDate],,1),if([Max_Dup]="Dup_AcctNo",RANKX(filter(ALL(Citris),[A_AcctNo]=EARLIER([A_AcctNo])),[ClosedDate],,1),1))

I like to determine; by either calculated column or measure the Days column -



A_ID A_CaseNo A_AcctNo A_CustName A_Zip Max_Dup ClosedDate Dup_Start_Month Rank Days
1778264 CA1402174 1234567890 Customer A 92805 Dup_Name_Zip 2/5/2014 0:00 2-Feb-14 1
1780331 CA1402537 1234567890 Customer A 92805 Dup_Name_Zip 3/3/2014 0:00 2 19
1789911 CA1404283 5555555555 Customer A 92805 Dup_Name_Zip 3/7/2014 0:00 3 5
1893359 CA1421247 1234567890 Customer A 92805 Dup_Name_Zip 10/23/2014 0:00 4 165
1816838 TX1407914 2345678910 Customer B 75240 Dup_Name_Zip 5/16/2014 0:00 5-May-14 1
1819378 TX1408289 Customer B 75240 Dup_Name_Zip 6/2/2014 0:00 2 12
1828786 TX1409758 2345678910 Customer B 75240 Dup_Name_Zip 6/13/2014 0:00 3 10
1828321 TX1409674 2345678910 Customer B 75240 Dup_Name_Zip 6/17/2014 0:00 4 3
1802583 MO1401660 3456789122 Customer C 63107 Dup_Name_Zip 4/3/2014 0:00 4-Apr-14 1
1803789 MO1401713 Customer C 63107 Dup_Name_Zip 4/25/2014 0:00 2 17
1817262 MO1402344 3456789122 Customer C 63107 Dup_Name_Zip 5/12/2014 0:00 3 12
1939532 MO1500599 3456789122 Customer C 63107 Dup_Name_Zip 2/12/2015 0:00 4 199
1900495 OK1402971 9876543210 Customer D 73072 Dup_AcctNo 11/4/2014 0:00 11-Nov-14 1
1904162 OK1403045 9876543210 Customer D 73072 Dup_AcctNo 11/12/2014 0:00 2 7
1907334 OK1403122 9876543210 Customer D 73072 Dup_AcctNo 11/14/2014 0:00 3 3
1920000 OK1403336 9876543210 Customer D 73072 Dup_AcctNo 12/19/2014 0:00 4 26
1920976 OK1403367 9876543210 Customer D 73072 Dup_AcctNo 12/22/2014 0:00 5 2
1782225 TX1402609 8765432100 Customer EE 77036 Dup_AcctNo 2/17/2014 0:00 2-Feb-14 1
1793899 TX1404321 8765432100 Customer EF 77036 Dup_AcctNo 3/18/2014 0:00 2 22
1797731 TX1404936 8765432100 Customer E 77036 Dup_AcctNo 4/1/2014 0:00 3 11
1810775 TX1406918 8765432100 Customer E 77036 Dup_AcctNo 5/1/2014 0:00 4 23
1817391 TX1407988 8765432100 Customer E 77036 Dup_AcctNo 5/8/2014 0:00 5 6

 

[scottsen]

I'm not SUPER getting your question, but two things:
* If you just subtract 2 dates, you get a # of days.
* If you need to respect holidays/weekends... then yes, it will be more complicated Probably don't a COUNTROWS on a FILTER() applied to the calendar table (possibly via DATESBETWEEN)... where holidays/weekends are filtered out.

 

[kF1894]

id	CaseNo	AcctNo	Custname	zip	Max_dup	ClosedDate	Dup_Strt_Month	Rank	Days
1778264	CA1402174</SPAN> <TBODY> </TBODY>	1234567890</SPAN> <TBODY> </TBODY>	Customer A</SPAN> <TBODY> </TBODY>	92805 <TBODY> </TBODY>	Dup_Name_Zip</SPAN> <TBODY> </TBODY>	2/5/2014 0:00</SPAN> <TBODY> </TBODY>	2-Feb-14</SPAN> <TBODY> </TBODY>	</SPAN>1 <TBODY> </TBODY>
1780331	CA1402537</SPAN> <TBODY> </TBODY>	1234567890</SPAN> <TBODY> </TBODY>	Customer A</SPAN> <TBODY> </TBODY>	92805	Dup_Name_Zip</SPAN> <TBODY> </TBODY>	3/3/2014 0:00</SPAN> <TBODY> </TBODY>		</SPAN>2 <TBODY> </TBODY>	19
1789911	CA1404283</SPAN> <TBODY> </TBODY>	5555555555</SPAN> <TBODY> </TBODY>	Customer A</SPAN> <TBODY> </TBODY>	92805	Dup_Name_Zip</SPAN> <TBODY> </TBODY>	3/7/2014 0:00</SPAN> <TBODY> </TBODY>		</SPAN>3 <TBODY> </TBODY>	5
1893359	CA1421247</SPAN> <TBODY> </TBODY>	1234567890</SPAN> <TBODY> </TBODY>	Customer A</SPAN> <TBODY> </TBODY>	92805	Dup_Name_Zip	10/23/2014 0:00</SPAN> <TBODY> </TBODY>		</SPAN>4 <TBODY> </TBODY>	165
1782225	TX1402609	8765432100</SPAN> <TBODY> </TBODY>	Customer EE</SPAN> <TBODY> </TBODY>	77036	Dup_AcctNo</SPAN> <TBODY> </TBODY>	2/17/2014 0:00</SPAN> <TBODY> </TBODY>	2-Feb-14</SPAN> <TBODY> </TBODY>	1
1793899	TX1404321</SPAN> <TBODY> </TBODY>	8765432100</SPAN> <TBODY> </TBODY>	Customer EF</SPAN> <TBODY> </TBODY>	77036	Dup_AcctNo</SPAN> <TBODY> </TBODY>	3/18/2014 0:00</SPAN> <TBODY> </TBODY>		2	22
1797731	TX1404936</SPAN> <TBODY> </TBODY>	8765432100</SPAN> <TBODY> </TBODY>	Customer E</SPAN> <TBODY> </TBODY>	77036	Dup_AcctNo</SPAN> <TBODY> </TBODY>	4/1/2014 0:00</SPAN> <TBODY> </TBODY>		3	11
1810775	TX1406918</SPAN> <TBODY> </TBODY>	8765432100</SPAN> <TBODY> </TBODY>	Customer E</SPAN> <TBODY> </TBODY>	77036	Dup_AcctNo</SPAN> <TBODY> </TBODY>	5/1/2014 0:00</SPAN> <TBODY> </TBODY>		4	23
1817391	TX1407988</SPAN> <TBODY> </TBODY>	8765432100</SPAN> <TBODY> </TBODY>	Customer E</SPAN> <TBODY> </TBODY>	77036	Dup_AcctNo</SPAN> <TBODY> </TBODY>	5/8/2014 0:00</SPAN> <TBODY> </TBODY>		5	6

<TBODY>
</TBODY>

Thanks for the reply - my apologies about the confusion, doesn't help when the data table is skewed. Let's try again. Customers are contacting duplicate times, need to determine the time between those contacts. The duplicate contacts are based on different criteria, AcctNo or Name& Zip. I have already solved for the number of times contacted by using rank, now I need to solve for how many days between contacts (Days column)