Need to calculate amount based on first bill date

[Anonymous]

There are five tables in my database:

Bill, Subscription, User, Credit card & Sites

In Subscription table, there is "type" column which contains below values:

1. Free (free)
2. Free plus (free)
3. Pro Comped (free)
4. Pro (paid = $15/mo)
5. Growth (paid = $29/mo)
6. Enterprise (paid = $99/mo)
7. Pro Managed (paid = $custom)

Over time our users have paid the varying amount within these subscriptions type. There is a “schedule” field where we tell you if it is a monthly or yearly plan.

You will also need to note that Bills have a column called “status.” The different statuses that a bill can have are:

1. Pending(this amount is considered as zero)
2. Paid(+)
3. Voided(+)
4. Failed(this amount is considered as zero)
5. Refunded (negative value)
6. Charged back (negative value)

I have set value according to above status in calculated column "Amount Corrected"

Required Calculation: Need to find by looking for sites that are paying their very first bill through one of our paid subscriptions. Note: for annual plans, we will need to split the payment into 12 equal parts. 

I have created a measure "New MRR", it shows aggregate value for all date, but I need to show value only for the first bill date.

Please Guide

Thanks in advance! 

Please find the .pbix file link:

https://www.dropbox.com/s/b7blkkv32dcmkzw/Leith.pbix?dl=0

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try below measures:

first bill at =
CALCULATE ( MIN ( bills[bill_at] ), ALL ( bills[bill_at] ) )

First MRR =
CALCULATE (
    [New MRR],
    FILTER ( ALL ( bills[bill_at] ), bills[bill_at] = [first bill at] )
)

Best regards,

Yuliana Gu