Forum Discussion

JJ2022PBI's avatar
JJ2022PBI
Helper I
4 years ago

Subscription Recurring Revenue

Hello,

 

I have a data table which is shows a list of transactions. Against each transaction is an annualised revenue figure where we will get this revenue into perpetutity.

 

Data Example:

 

 

If I have selected June 2022 on my slicer I would like to show 9 days worth of revenue from transaction 1 and 30 days worth of revenue from transaction 2.

 

Likewise if I selected July 2022 I would like to show 31 days of revenue from both transaction 1 and transaction 2. 

 

I would like to calculate this using DAX.

 

As the Month & Year of June 2022 is on the slicer I only want to show transactions that have occured since 01/01/2022.

 

Thank you for your help!

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    JJ2022PBI , try this measure:

     

    AnnualisedRev = 
    VAR vAnnualDate = SELECTEDVALUE('Fact'[Date])
    VAR vRevenuePerDay = SELECTEDVALUE('Fact'[Annualised Revenue]) / 365
    VAR vFirstDate = FIRSTDATE(DateDim[Date])
    VAR vLastDate = LASTDATE(DateDim[Date])
    VAR vDays = DATEDIFF(MAX(vAnnualDate, vFirstDate), vLastDate, DAY) + 1
    RETURN vRevenuePerDay * vDays

     

    where 'DateDim'[Date] is the field you use for you date slicer, and 'Fact' is the table where you have the other data.

    For June this gives me

     

    and for July it gives me

     

  • JJ2022PBI's avatar
    JJ2022PBI
    Helper I
    4 years ago

    Thank you Anonymous, based on your results that is the outcome I am looking for however it isn't working in my model, I am getting tiny values.

     

    I have updated the tables in your formula to the below. My fact table (Wholesale Actuals) will have about 200,000 transactions every month into a range of funds. Using a mapping table I map these funds to strategies and I would show that on my dashboard, not the individual transaction.

     

    Would I need to use some form of iterating function like sumx to calculate the in month revenue of each transaction and then somehow add on the normal full month of revenue?

     

    Thank you for help.

     

     

    AnnualisedRev =
    VAR vAnnualDate = SELECTEDVALUE('Wholesale Actuals'[Flow Date])
    VAR vRevenuePerDay = SELECTEDVALUE('Wholesale Actuals'[Annualised Revenue]) / 365
    VAR vFirstDate = FIRSTDATE(Dates[Date])
    VAR vLastDate = LASTDATE(Dates[Date])
    VAR vDays = DATEDIFF(MAX(vAnnualDate, vFirstDate), vLastDate, DAY)
    + 1 RETURN vRevenuePerDay * vDays