cancel
Showing results for
Did you mean:
Helper I

## Using Multiple Dates to calculate base size

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

1 ACCEPTED SOLUTION

Accepted Solutions
Helper I

## Re: Using Multiple Dates to calculate base size

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()))     )`

5 REPLIES 5
Highlighted
Microsoft

## Re: Using Multiple Dates to calculate base size

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

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

## Re: Using Multiple Dates to calculate base size

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

Microsoft

## Re: Using Multiple Dates to calculate base size

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

## Re: Using Multiple Dates to calculate base size

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

Helper I

## Re: Using Multiple Dates to calculate base size

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()))     )`

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors