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 same Revenue numbers for MTD, QTD and YTD.EmailNameSubscription Price (Monthly)Subscription Start DateSubscription End DateSubscription Statusabc.xyz@gmail.comAbc803-Jan-202105-Jan-2021Cancelledklen@gmail.comKlen1222-Feb-2021 Activedavid@gmail.comDavid822-Feb-202122-Mar-2021CancelledPhillip@gmail.comPhilip822-Mar-202122-Jul-2021Cancelledkenn.f@gmail.comKenn F618-Apr-2021 Activeronald.g@gmail.comRonald G7.522-Apr-2021 Activeelmo.b@gmail.comElmo413-May-202113-Jun-2021Cancelledsonia@gmail.comSonia22.530-May-2021 Activekatie@gmail.comKatie631-May-2021 Activediana@gmail.comDiana1030-Jun-2021 Activelawrence@gmail.comLawrence530-Jun-202130-Jul-2021Cancelledvalentine@gmail.comValentine1010-Jul-2021 Activescott@gmail.comScott2712-Jul-2021 Activecarine@gmail.comCarine21614-Jul-2021 Activejason@gmail.comJason5215-Jul-2021 Activeallwyn@gmail.comAllwyn626-Jul-2021 Activerex@gmail.comRex2627-Jul-2021 Activeelizabeth@gmail.comElizabeth829-Jul-2021 Activesierra@gmail.comSierra1102-Aug-2021 Activesharon@gmail.comSharon13.503-Aug-2021 Active Revenue = CALCULATE(SUM(Sheet1[Subscription Price (Monthly)]),FILTER(ALL(Sheet1),(Sheet1[Subscription Start Date]< Max('Date'[Date]) && Sheet1[Subscription End Date] >= Max('Date'[Date])) || Sheet1[Subscription Start Date] < Max ( 'Date'[Date] ) && ISBLANK ( Sheet1[Subscription End Date]))) Revenue MTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFMONTH('Date'[Date]),TODAY()))Revenue QTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFQUARTER('Date'[Date]),TODAY()))Revenue YTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFYEAR('Date'[Date]),TODAY())) Thanks,Sonia

Frequent Visitor

5 years ago

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 same Revenue numbers for MTD, QTD and YTD.

Email	Name	Subscription Price (Monthly)	Subscription Start Date	Subscription End Date	Subscription Status
[email protected]	Abc	8	03-Jan-2021	05-Jan-2021	Cancelled
[email protected]	Klen	12	22-Feb-2021		Active
[email protected]	David	8	22-Feb-2021	22-Mar-2021	Cancelled
[email protected]	Philip	8	22-Mar-2021	22-Jul-2021	Cancelled
[email protected]	Kenn F	6	18-Apr-2021		Active
[email protected]	Ronald G	7.5	22-Apr-2021		Active
[email protected]	Elmo	4	13-May-2021	13-Jun-2021	Cancelled
[email protected]	Sonia	22.5	30-May-2021		Active
[email protected]	Katie	6	31-May-2021		Active
[email protected]	Diana	10	30-Jun-2021		Active
[email protected]	Lawrence	5	30-Jun-2021	30-Jul-2021	Cancelled
[email protected]	Valentine	10	10-Jul-2021		Active
[email protected]	Scott	27	12-Jul-2021		Active
[email protected]	Carine	216	14-Jul-2021		Active
[email protected]	Jason	52	15-Jul-2021		Active
[email protected]	Allwyn	6	26-Jul-2021		Active
[email protected]	Rex	26	27-Jul-2021		Active
[email protected]	Elizabeth	8	29-Jul-2021		Active
[email protected]	Sierra	11	02-Aug-2021		Active
[email protected]	Sharon	13.5	03-Aug-2021		Active

Revenue = CALCULATE(SUM(Sheet1[Subscription Price (Monthly)]),FILTER(ALL(Sheet1),(Sheet1[Subscription Start Date]< Max('Date'[Date]) && Sheet1[Subscription End Date] >= Max('Date'[Date])) || Sheet1[Subscription Start Date] < Max ( 'Date'[Date] ) && ISBLANK ( Sheet1[Subscription End Date])))

Revenue MTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFMONTH('Date'[Date]),TODAY()))

Revenue QTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFQUARTER('Date'[Date]),TODAY()))

Revenue YTD = CALCULATE([Revenue],DATESBETWEEN('Date'[Date],STARTOFYEAR('Date'[Date]),TODAY()))

Thanks,

Sonia

Need Help

v-kelly-msft

5 years ago

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

Did I answer your question? Mark my reply as a solution!

Subscription data - Revenue and Active Subscribers calculations.pbix67 KB

5 Replies

lbendlin

Super User

5 years ago

Your "Subscription price (monthly)" looks more like it is applied to the period from the start date of the subscription, not from the fist day of the month, right?

With that - what is your expectation on the subscription cost for [email protected] ? Or anyone else who cancels their subscription before the end of the cycle?

soniajs

Frequent Visitor

5 years ago

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

5 years ago

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

Did I answer your question? Mark my reply as a solution!

Subscription data - Revenue and Active Subscribers calculations.pbix67 KB

lbendlin
Super User
5 years ago
What if [email protected] would have canceled on 23-Mar-2021 - would that be 8 or 16 ?

Forum Discussion

Subscription data - Revenue and Active Subscribers calculations

5 Replies

Related content

POWERBI Subscription

Fabric Subscription

Dynamic Subscription

Excel Subscription

Subscription report