Forum Discussion

soniajs's avatar
soniajs
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 s...
Need Help
  • v-kelly-msft's avatar
    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!

lbendlin's avatar
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's avatar
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's avatar
    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!

    • soniajs's avatar
      soniajs
      Frequent Visitor
      5 years ago

      Thank you.