Forum Discussion

mbsptr's avatar
mbsptr
Frequent Visitor
4 years ago
Solved

Subscriptions Revenue Totals

Hello,

 

I am currently trying to calculate total subscription revenue for subscriptions based on the months filtered, but having trouble getting the totals to add up correctly (specifically the revenue measure in sample data). 

I am able to get the correct total total for monthly subscription costs using the measure below, but need to then multiple this by the subscription months:

 

VAR CurrentDate = MAX(DateDim[Date])
var CurrentDate1 = MIN(DateDim[Date])
var TotalValue =
CALCULATE(SUM(SubscriptionBillingValue[Billing Value]),
ALL(DateDim),
SubscriptionBillingValue[Subscription Start Date]<=CurrentDate,
SubscriptionBillingValue[Subscription End Date]>=CurrentDate1)
return
TotalValue

 

 

Sample data can be foud here:

https://drive.google.com/file/d/1CQxJt4gBTvWZZ5G256KN83RTkfWIF284/view?usp=sharing

 

Thank you,

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, mbsptr ;

    You should change the measure of Number of Subscriptions. if we select the order from 1 to 6.(A small amount of data makes it easier to see.）

    we could only order 2 Meet the conditions.

    so we should change the Number of Subscriptions to this.

    Number of Subscriptions = 
var CurrentDate = MAX(DateDim[Date])
var CurrentDate2 = MIN(DateDim[Date])
var ActiveSubscriptions = 
CALCULATE(DISTINCTCOUNT(SubscriptionBillingValue[Clip_Factory_Purchase_Order__c]), 
DateDim,
SubscriptionBillingValue[Subscription Start Date]<=CurrentDate, 
SubscriptionBillingValue[Subscription End Date]>=CurrentDate2
)
return
ActiveSubscriptions

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    I haven't looked at your data but you can get the months with the datediff function. Use it as a variable and return total * months if thats what I think you're saying you need.

    months = datediff(CurrentDate1,CurrentDate,MONTH)

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, mbsptr ;

    You should change the measure of Number of Subscriptions. if we select the order from 1 to 6.(A small amount of data makes it easier to see.）

    we could only order 2 Meet the conditions.

    so we should change the Number of Subscriptions to this.

    Number of Subscriptions = 
var CurrentDate = MAX(DateDim[Date])
var CurrentDate2 = MIN(DateDim[Date])
var ActiveSubscriptions = 
CALCULATE(DISTINCTCOUNT(SubscriptionBillingValue[Clip_Factory_Purchase_Order__c]), 
DateDim,
SubscriptionBillingValue[Subscription Start Date]<=CurrentDate, 
SubscriptionBillingValue[Subscription End Date]>=CurrentDate2
)
return
ActiveSubscriptions

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.