How to Pull contracts with Dates that are expiring within a week

[Anonymous]

I have contracts that are about to expire. As shown in the excel snap below, I want to add an expression that will identify only those contracts (contract end date will be the relevant field) that expire within a week. How do I do that.

[Anonymous]

Function

InWeek("Contract End Date", today(),1)

returns 'true' for the records where the "Contract End Date" falls within the next week.  Just use it as a condition.

[ramoncova06]

another option could be

only({<[Contract End Date] = {'>=$(=(today()+7))'}>}[Contract #])

[Anonymous]

Sorry John, I don't understand now...
Can you explain why 126-A is white and 133-A is green, although they both expire next week (4/8/2015) and both are Active?

[ramoncova06]

I am not sure how to added them into Michael's solution, but you could just exclude the values from the field in your set analysis

only({<[Contract End Date] = {'>=$(=(today()+7))'}, [Contract Status] -={'Active', 'Signed'}>}[Contract #])

[Anonymous]

Michael the reference point here are not 126-A or 133-A which are contract numbers. The Point of ref is Product ID. 126-A with Product ID 325894 is expiring on 4/8/2015 and there is no new contract backing it up. thus it sud be captured.  Whereas 133-A with Product ID 328773 has a new contract under 134-A starting on 4/9/2015. Therefore this is a good one and sud be excluded. let me know if its not clear, I will explain again. thanks

[Anonymous]

any update Michael?

[Anonymous]

Quite clear.  I wish if you uploaded your application so I could try.  This is what I think should work, but I didn't test, there are chances of mistakes:

only({<"Contract #"={"=InWeek("Contract End Date", today(),1)"}> - <"Product ID"=P({<"Contract End Date"={">date(weekend(today()+7))"}, "Contract Status"={'Active','Signed'}>})>} "Product ID")

If it doesn't work, upload you app, or at least the data.  Hopefully I'll find time to try (or anybody else will).

[Anonymous]

I am going to change the Question I was asking because I figured I was asking the wrong question! guilty as charged. I have also changed my data but the logic & scenario have remained the same.

The fields in the Excel sheet attached are:

CustomerID, Contract#, Contract Status, Start Date & End Date. !

So once I pull data using Michael's suggestion, how about filtering those customers by adding a calculated dimension that goes like this:-- if a customerID has a contract end date that is greater than the date(one week from today), then exclude that customer ID. So as you can see, the Contract status is redundant. As long as the contract is not ending in a 1 week time, that particular customerID is good for now. But if that customerID does not have a contract after 1 week, he needs coverage. So to drill to the solution, what syntax can we come up with? If exclude is complex, may be the calculated dimension can include those Customer IDs that have end dates in a week from today

[Anonymous]

Here is the file with the data