Genius Required!!!

[JEB85]

Hi Guys,

[TABLE="width: 561"]
<tbody>[TR]
[TD]- I receive a monthly extract that details all of our contract data for that period
[/TD]
[/TR]
[TR]
[TD]- Each month I upload this data into Powerpivot (copy and paste to append rows)
[/TD]
[/TR]
[TR]
[TD]- I would like to be able to summarise the monthly movement in contracts (see desired result)
[/TD]
[/TR]
[TR]
[TD]- If the contract existed this month and last month then it's a 'Continued' contract
[/TD]
[/TR]
[TR]
[TD]- If the contract exists this month but didn't last month then it's a 'New' contract
[/TD]
[/TR]
[TR]
[TD]- If the contract existed last month but doesn't this month then it's a 'Lost' contract
[/TD]
[/TR]
[TR]
[TD]- One contract can be spread across multiple regions so I'd like to be able to slice by region

Example of Data Set:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Contract[/TD]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Month[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Jan-12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Feb-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]Mar-12[/TD]
[/TR]
</tbody>[/TABLE]

This shows which contracts exist in each month:

[TABLE="width: 332"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Region[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract[/TD]
[TD="align: right"]Jan-12[/TD]
[TD="align: right"]Feb-12[/TD]
[TD="align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]
This shows the status of each contract:

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]Jan-12[/TD]
[TD="align: right"]Feb-12[/TD]
[TD="align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]New[/TD]
[TD]Lost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]New[/TD]
[TD]Continued[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]New[/TD]
[TD]Continued[/TD]
[TD]Continued[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD]New[/TD]
[TD]Continued[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD]New[/TD]
[TD]Continued[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD][/TD]
[TD][/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desired Result:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]Jan-12[/TD]
[TD="align: right"]Feb-12[/TD]
[TD="align: right"]Mar-12[/TD]
[/TR]
[TR]
[TD]Opening[/TD]
[TD]-[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Closing[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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!