Translate formula into Power Pivot

[frankee_gee]

Hope some one can help with this formula into Power Pivot:

I use this in an excel spreadsheet.
=SUMPRODUCT((I2:I2<=$AO$1)*(((J2:J2="")+(J2:J2>=$AO$1)>=1)))

$AO$1 is a date. we can replace with actual date of need be: We have separate columns for each 1/31/2014, 2/28/2014 & 3/31/2014.


or

I was told to use this instead but the above work for me. I did not test this.

=IF(OR(AND(J2<$AL$1,K2>$AL$1),AND(Z2="Erect",K2="")),1,0)


Thank you,

Frankee Gee.

 

[theBardd]

I agree with whoever told you, SUMPRODUCT is pointless there, although I think it should be

=--(AND(I2:I2<=$AO$1,OR(J2:J2="",J2:J2>=$AO$1)))

You want this as a calculated column? What does the PowerPivot model look like, what tables, what columns, and how do these map onto the cells in your formula?

 

[frankee_gee]

Bardd,

Thanks for the reply.

One table: ProfieldC

Two columns:
1. ErectDate
2. DismantleDate
comparing to this date: 1/31/2014. I will also compare to 2/28/2014 & 3/31/2014. In other words this tells us how many rentals are still out on rent.


=SUMPRODUCT((ErectDate<=1/31/2014)*(((DismantleDate="")+(DismantleDate>=1/31/2014)>=1)))

What I'm trying to do is go back in time to capture information from the past.

 

[theBardd]

The Jan Test would be

=AND(ProfieldC[ErectDate]<=DATE(2014,1,31),OR(ISBLANK([DismantleDate]),ProfieldC[DismantleDate]>DATE(2014,1,31))))

giving TRUE or FALSE, or

=(AND(ProfieldC[ErectDate]<=DATE(2014,1,31),OR(ISBLANK([DismantleDate]),ProfieldC[DismantleDate]>DATE(2014,1,31))))*1

giving 1 or 0.

And the Mar test would be

=AND(ProfieldC[ErectDate]<=DATE(2014,1,31),OR(ISBLANK([DismantleDate]),ProfieldC[DismantleDate]>DATE(2014,1,31))))

or

=(AND(ProfieldC[ErectDate]<=DATE(2014,3,31),OR(ISBLANK([DismantleDate]),ProfieldC[DismantleDate]>DATE(2014,3,31))))*1

 

[frankee_gee]

Bardd,

Tested the 2nd, line. Looks like this worked. This helps to verify that the correct rows are being captured.

Can this original formula be converted to the original request, which was this :

=SUMPRODUCT((I2:I2<=$AO$1)*(((J2:J2="")+(J2:J2>=$AO$1)>=1)))

Might look something like this based on your help above:

=SUMPRODUCT((ProfieldC[ErectDate]<=DATE(2014,1,31)*(((ISBLANK[DismantleDate)+(ProfieldC[DismantleDate]>=DATE(2014,1,31)>=1)))

Thank you for your help. Your time is appreciated,

Frankee

 

[theBardd]

I can't see what is different between what you now are asking and what you last asked.

 

[frankee_gee]

Bardd,

I just figured out why I'm getting too many rows back. I left out important item: [ActivityType]="Erect" how can this be added. I tried but I got error:

=(AND(ProfieldW[ErectDate]<=DATE(2014,1,31),AND(ProfieldW[ActivityType]="Erect"),OR(ISBLANK([DismantleDate]),ProfieldW[DismantleDate]>DATE(2014,1,31))))*1

Thanks for your time,

Frankee Gee.