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
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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors