How to exclud expired and cancelled records over time using a filter

[ieckert]

I have a powerpivot orders table that contains records with three statuses Live, Expired and Cancelled. The records also include the number of product recipients for each order. I also have a date table (Dim Date). I have been trying to work out a way to be able to count the number of recipients at any point any particular point in time.

In terms of the information I have the following:

The current status of the order - live, expired of cancelled
The start date of the order
The expiry date of the order
The cancellation date (which will be after the start date but before the expiry date)

What I want to be able to do is to show how many recipients there were at any point in the data's history. So I have to be able to deal with the fact that a record that is now cancelled or expired would have been live prior to it's expiration or cancellation date. I also need to deal with the fact that some Live orders may be in the future and therefore should not be counted until they have reached their start date

So far I've come up with this:


CurrentLiveRecipients (old measure):=CALCULATE (
SUM ( CVM_dashboard_new_or_renewal_historic[UNIQUE_RECIPIENTS] ),
FILTER (
ALL ( CVM_dashboard_new_or_renewal_historic ),
CVM_dashboard_new_or_renewal_historic[Cancellation_Date]
>= TODAY ()
), FILTER (
ALL ( CVM_dashboard_new_or_renewal_historic ),
CVM_dashboard_new_or_renewal_historic[Expiry_Date]
>= TODAY () && CVM_dashboard_new_or_renewal_historic[STATUS]="Live"
),
FILTER (
ALL ( DimDate ),
DimDate[Date Value] <= MAX ( DimDate[Date Value] )
)
)

But I simply cannot get this to give me the number I am expecting. Instead it seems to give me a cumulative total.

Can anybody help me work out where am I going wrong?

 

[scottsen]

the magic google query you are looking for is "PowerPivot Events In Progress" -- well, but without the quotes. Several great articles on it.