Forum Discussion
Solved
Subscription data - Revenue and Active Subscribers calculations
Hi, I have a dataset like below. So, I want to calculate the Revenue generated for MTD,QTD,YTD and want to show it in different cards. I've shared the formula's I'm using below. But I'm getting s...
Hi soniajs ,
Create 3 measures as below:
MTD = VAR _tab = CALCULATETABLE ( VALUES ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[Month] = SELECTEDVALUE ( 'Date'[Month] ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Subscription Price (Monthly)] ), FILTER ( ALL ( 'Table' ), 'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] ) && ( 'Table'[Subscription End Date] >= MINX ( _tab, [Date] ) || 'Table'[Subscription End Date] = BLANK () ) && 'Table'[Email] = MAX ( 'Table'[Email] ) ) )QTD = VAR _tab = CALCULATETABLE ( VALUES ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[Quarter] = SELECTEDVALUE ( 'Date'[Quarter] ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Subscription Price (Monthly)] ), FILTER ( ALL ( 'Table' ), 'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] ) && ( 'Table'[Subscription End Date] >= MINX ( _tab, [Date] ) || 'Table'[Subscription End Date] = BLANK () ) && 'Table'[Email] = MAX ( 'Table'[Email] ) ) )YTD = VAR _tab = CALCULATETABLE ( VALUES ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Subscription Price (Monthly)] ), FILTER ( ALL ( 'Table' ), 'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] ) && ( 'Table'[Subscription End Date] >= MINX ( _tab, [Date] ) || 'Table'[Subscription End Date] = BLANK () ) && 'Table'[Email] = MAX ( 'Table'[Email] ) ) )And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
Yes, it depends on the period from the start date of the subscription. For [email protected] , Jan month Revenue should be $8.
v-kelly-msft
Community Support
Community SupportHi soniajs ,
Create 3 measures as below:
MTD =
VAR _tab =
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
FILTER (
ALL ( 'Date' ),
'Date'[Month] = SELECTEDVALUE ( 'Date'[Month] )
&& YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Subscription Price (Monthly)] ),
FILTER (
ALL ( 'Table' ),
'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] )
&& (
'Table'[Subscription End Date] >= MINX ( _tab, [Date] )
|| 'Table'[Subscription End Date] = BLANK ()
)
&& 'Table'[Email] = MAX ( 'Table'[Email] )
)
)
QTD =
VAR _tab =
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
FILTER (
ALL ( 'Date' ),
'Date'[Quarter] = SELECTEDVALUE ( 'Date'[Quarter] )
&& YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Subscription Price (Monthly)] ),
FILTER (
ALL ( 'Table' ),
'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] )
&& (
'Table'[Subscription End Date] >= MINX ( _tab, [Date] )
|| 'Table'[Subscription End Date] = BLANK ()
)
&& 'Table'[Email] = MAX ( 'Table'[Email] )
)
)
YTD =
VAR _tab =
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
FILTER ( ALL ( 'Date' ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) )
)
RETURN
CALCULATE (
SUM ( 'Table'[Subscription Price (Monthly)] ),
FILTER (
ALL ( 'Table' ),
'Table'[Subscription Start Date] <= MAXX ( _tab, [Date] )
&& (
'Table'[Subscription End Date] >= MINX ( _tab, [Date] )
|| 'Table'[Subscription End Date] = BLANK ()
)
&& 'Table'[Email] = MAX ( 'Table'[Email] )
)
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Thank you.