Genius Required!!!

[JEB85]

Hi Guys,

- I receive a monthly extract that details all of our contract data for that period

- Each month I upload this data into Powerpivot (copy and paste to append rows)

- I would like to be able to summarise the monthly movement in contracts (see desired result)

- If the contract existed this month and last month then it's a 'Continued' contract

- If the contract exists this month but didn't last month then it's a 'New' contract

- If the contract existed last month but doesn't this month then it's a 'Lost' contract

- One contract can be spread across multiple regions so I'd like to be able to slice by region Example of Data Set: Contract Region Month A 1 Jan-12 A 2 Jan-12 B 1 Jan-12 C 3 Jan-12 C 4 Jan-12 C 5 Jan-12 C 6 Jan-12 D 1 Jan-12 D 5 Jan-12 D 6 Jan-12 B 1 Feb-12 C 3 Feb-12 C 6 Feb-12 D 1 Feb-12 D 5 Feb-12 D 6 Feb-12 E 1 Feb-12 F 1 Feb-12 D 1 Mar-12 D 5 Mar-12 D 6 Mar-12 E 1 Mar-12 F 1 Mar-12 H 1 Mar-12 H 5 Mar-12 H 6 Mar-12 I 3 Mar-12 J 1 Mar-12 J 5 Mar-12 K 2 Mar-12 K 4 Mar-12 L 6 Mar-12 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody> </tbody> This shows which contracts exist in each month: Region 1 Month Contract Jan-12 Feb-12 Mar-12 A 1 B 1 1 D 1 1 1 E 1 1 F 1 1 H 1 J 1 Grand Total 3 4 5 This shows the status of each contract: Region 1 Jan-12 Feb-12 Mar-12 A New Lost B New Continued Lost D New Continued Continued E New Continued F New Continued H New J New Desired Result: Region 1 Jan-12 Feb-12 Mar-12 Opening - 3 4 New 3 2 2 Lost 0 1 1 Closing 3 4 5 <colgroup><col><col span="3"></colgroup><tbody> </tbody>

<tbody>
</tbody>

The desired result above shows how I'd like the powerpivot to look. I have no idea how to get it to that stage though.

Anyone any ideas?

Thanks

 

[JavierGuillen]

Hi Jeb85,

I recently blogged about a technique that should be helpful in your case. On it, I showed how to determine acquired or lost customers from month to month. You can use the same technique to deal with contract/region data and achieve the output you require;

The link is the following: Determining lost or acquired customers in DAX « Javier Guillén

Javier Guillen

 

[swaran_fca]

If you need only desired result table, then countif formula would help you:

=COUNTIF(A1:A5,"NEW")

for example if there are 3 instances of "NEW" in range A1:A5, then the result of the formula would be 3

likewise for lost =COUNTIF(A1:A5,"LOST")

As I understand from your example, "CONTINUED" does not have any impact on the calculation.

Let me know if this is what you need.

Regards,
Swaran

 

[JEB85]

Javier - thank you very much, that looks to be exactly what I'm after! I will try it out at work tomorrow and let you know!

Swaran - thanks but my data set is huge and continuing to grow so definitely need a powerpivot solution.

 

[JEB85]

Javier - your solution worked brilliantly - thanks!!!!!!

 

[JavierGuillen]

Great to hear it helped!