Trying to Predict Monthly/Quarterly/Yearly Payments using DAX

[wmcclure]

Hello all! 

My colleague and I are trying to find a way to calculate the expected payments accross all of our subscriptions throughout a given timeframe using the following variables
 - The Start Date for the subscription

 - The End Date of the subscription

 - The Billing Cycle (Monthly, Quarterly, or Yearly)
 - The Amount to be billed on each cycle

For now, I will focus only on monthly billing, as the other I imagine will use the same logic for the most part.

If I have 3 contracts,
 - one beginning on 1/1/2024 with $100 monthly payments
 - one beginning on 2/1/2024 with $150 Monthly Payments

 - one beginning on 3/1/2024 with $200 Monthly Payments

I would expect a visual showing a MRR of $100 for January, $250 for February, and $450 for March. However, the functions I have been experimenting with all seem to only show the payment on the starting month of each contract (Jan $100, Feb $150, mar $200)

The end result we are looking for is to have a stacked bar chart visual that shows the total expected (upcoming) payments for active contracts for each billing cycle type. Are there any resources someone can point me to for solving this? Thanks in advance for any and all advice!

[v-denglli-msft · Accepted solution]

Hi @wmcclure,

Based on the information you provided I created the following data table and a calendar table (containing dates throughout the year 2024).

You can use the following DAX to create MRR measures.

MRR = CALCULATE(
    SUM(Subscriptions[Amount]),
    FILTER(
        Subscriptions,
        Subscriptions[Start Date]<=MAX('Calendar'[Date]) && Subscriptions[End Date]>=MIN('Calendar'[Date])
    )
)

Create a stacked column chart using the MRR measure values and the date column in the calendar table.
This chart shows the expected monthly MRR for 2024.

Please see the attached PBIX for reference.

Best Regards,

Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.