Tricky Calculate Question

[JEB85]

Hi,

I have a huge data set in Powerpivot in the structure below:

[TABLE="width: 348"]
<TBODY>[TR]
[TD]Contract</SPAN>[/TD]
[TD]Reporting Month</SPAN>[/TD]
[TD]Established</SPAN>[/TD]
[/TR]
[TR]
[TD]AAA</SPAN>[/TD]
[TD="align: right"]30/09/2012</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[/TR]
[TR]
[TD]AAA</SPAN>[/TD]
[TD="align: right"]31/10/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>[/TD]
[TD="align: right"]30/09/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>[/TD]
[TD="align: right"]31/10/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
[TR]
[TD]CCC</SPAN>[/TD]
[TD="align: right"]30/09/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
[TR]
[TD]DDD</SPAN>[/TD]
[TD="align: right"]30/09/2012</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[/TR]
[TR]
[TD]DDD</SPAN>[/TD]
[TD="align: right"]31/10/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
[TR]
[TD]EEE</SPAN>[/TD]
[TD="align: right"]31/10/2012</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[/TR]
[TR]
[TD]FFF</SPAN>[/TD]
[TD="align: right"]31/10/2012</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


I need to calculate how many contracts:
- Existed in the previous month
- Had an Established reason of 'No' in the previous month
- Has an Established reason of 'Yes' this month

Based on the sample data set above and from the perspective of the reporting month 31/10/2012:

Answer = 2 (AAA and DDD)

Anyone any ideas?

Thanks

 

[MD610]

First I would add a Calendar/Date table to your data model. This is needed to correctly do time calculations. It only requires 1 column that contains a running list of all dates from the minimum date of your data set to the maximum date of your data set. It must list every date even if your data only shows activity on the last day of the month. Given you specific issue, the date table could also contain a column showing the month as well. Once imported to PowerPivot, relate the date field to your reporting month field.

Then, I would break this problem down into a few simple measures that build on top of one another.

1. Contract Cnt:=DISTINCTCOUNT(Table[Contract])

2. Contracts Prev Mnth:=CALCULATE([Contract Cnt], PREVIOUSMONTH(Dates[Dates]))

3. Previous with No:=CALCULATE([Contracts Prev Mnth], Table[Established] = "No")

4. Current with Yes:=CALCULATE([Contract Cnt], Table[Established] = "Yes")

5.

Final Measure:=SUMX(
                                  VALUES(Table4[Contract]),
                                              IF(
                                                     [Previous with No] = [Current with Yes] && 
                                                     [Previous with No]+[Current with Yes] >0,
                                                     1
                                              )
                           )

You could probably consolidate a lot of this into a single measure but I think it is much easier to follow and understand when broken down into pieces.

The other measures don't need to be included in your pivot for [Final Measure] to do its job.

Let me know if this helps.

 

[JEB85]

Thanks Mike, I will give it a run tomorrow and let you know the result!

 

[JEB85]

Mike - just to let you know your solution worked perfectly!

Thanks a lot!