Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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()))
)
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
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
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
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
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()))
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |