Using Multiple Dates to calculate base size

[Greenwoodr]

Hi,

I am looking for some help with using multiple dates to calculate the size of a subscription base at any given point in time.

I have the following tables (all dates use the Date ID rather than actual date but I have put dates in here to illustrate):

I need to be able to say how many are open at the end of any given period which would be calculated as 

Active = (sum(PaidCount) where Paid Date <= End of Curent Period & (enddate > end of current period OR end date is NULL)

giving the following result

My question is what is the best way to achieve this in Power BI?

Many Thanks

[v-cherch-msft]

Hi @Greenwoodr

Try below measure.If it is not your case,please share more about the sample data and list the P2,P3 period.You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Active =
CALCULATE (
    SUM ( 'Fact'[PaidCount] ),
    FILTER (
        'Fact',
        'Fact'[PaidDate] <= MAX ( 'Date'[EndofPeriod] )
            && 'Fact'[EndDate] > MAX ( 'Date'[EndofPeriod] )
            || 'Fact'[EndDate] = BLANK ()
    )
)

Regards,

Cherie

[Greenwoodr]

Hi Cherie,

Many Thanks - this feels nearly there but as my tables include the Date ID rather than the actual date it does not work.

My Calculation would look more like this

Active =
CALCULATE (
    SUM ( 'Fact'[PaidCount] ),
    userelationship('Fact'[PaidDateID],'Date'[DateID],
    FILTER (
        'Fact',
        'Fact'[PaidDateID] <= MAX ( 'Date'[EndofPeriod] )
            && 'Fact'[EndDateID] > MAX ( 'Date'[EndofPeriod] )
            || 'Fact'[EndDateID] = BLANK ()
    )
)

What is the best way to replace the PaidDateID and EndDateID in this calcualtion with the actual dates? 

(I guess I could add the actual Date to the fact table but this doesnt feel that efficient!!)

Again Many Thanks 

Richard

[v-cherch-msft]

Hi @Greenwoodr

Attached the simplified sample file for your reference.Please explain more about your issue with the sample file as i'm not clear about the actual data and expected output.

Regards,
Cherie

[Greenwoodr]

Hi,

What I am trying to achieve is a count of open subscriptions at the end of end given month. 

Each subscription has 3 dates 1) SignUp Date, 2) Paid Date and 3) Enddate and I need to know at the end of each month how many are paid and have not ended:

For Example:

Yellow : Paid 25/11/2004 and has no end date therefore will be open at end of November, Dec,Jan .....

Blue : Paid 30/11/2004 and ended on 19/1/2005  is open therefore will be open at end of November & Dec Only

Green : Paid 01/12/2004  and ended on 14/1/2005 and is open therefore will be open at end of Dec Only

I am therefore looking for a table that shows

I have set this up in Power BI as the following (where the active relationship is based on original sign up date):

The Subscriptions file contains the 3 dates (linked to a date table by id numbers) & the date table contains an end of current month date;

I need therefore to evaluate each row for each month end to work out whether the subscription is active where

Active = (paiddate <= Currrent_Month_end_Date) and (enddate > Current_Month_end_date OR enddate is Blank)

however using the following code

Active = 
    calculate (sum(Subscriptions[paidsupporter]), 
    filter(Subscriptions,Subscriptions[PaidDate]<=max('Date'[Current_month_end_Date])
    && (Subscriptions[ENDDATE]>max('Date'[Current_month_end_Date])
    || Subscriptions[ENDDATE]=blank())))

gives me 

which is not the same as the I was expecting (the table above (6,8,10,4,3,3)

Richard

[Greenwoodr · Accepted solution]

Hi, 

I have now got this working - If include all in the filter then this works like a dream 

Many Thanks

Active ALL CG = 
calculate 
     (
          sum('subsctiptions'[Paidsupporter]), 
             filter(all('subscriptions'),
               'subscriptions'[Paiddate]<=max('Date Dimension'[Current_month_end_Date])
                && ('subscriptions'[ENDDATE]>max('Date Dimension'[Current_month_end_Date])
                || 'subscriptions'[ENDDATE]=blank()))
     )