cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Greenwoodr Helper I
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):

 

Capture.JPG

 

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

 

Capture2.JPG

 

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

Many Thanks

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Greenwoodr Helper I
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()))
)

 

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

Re: Using Multiple Dates to calculate base size

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

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.
Greenwoodr Helper I
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
Microsoft

Re: Using Multiple Dates to calculate base size

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

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.
Greenwoodr Helper I
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:

BI_Excel.JPG

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

BI_Excel2.JPG

 

 

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

BI Picture.JPG

 

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 

BI.JPG

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

 

Richard

 

 

Greenwoodr Helper I
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()))
)

 

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors