Handling revenue from subscription with a date range on a monthly basis

[Matjo]

Hi, 

I have some data with subscription where the subscription can start at the beginning or the middle of the month.

So it could look like this. 

Company   Monthly_fee start_date       end_date
1337ltd             99          2015-02-14    2017-03-31

I would like to be able to slice this income on a monthly basis so if I would filter for January 2017 I would get $99 as revenue for this company, the revenue for february 2015 should be $49.5. 

My initial thought was to create additional columns and pivot them to get an entry for each month but since the contracts can start in the middle of the month that does not work. 

Any ideas how this can be handled in an elegant way?

Best regards!

[KHorseman]

Can you give some details on the rules for how the fee is prorated? Is it reduced by the percentage of a month they are subscribed, is it like a tiered thing where for instance if they are subscribed for less than 3 weeks they get 50% of the fee, etc?

[Matjo]

If the subscription starts or ends in the middle of a month the fee for that month is 50% so $49.5.

if a subscription looked like this: 
CompanyA $99 15/1/2017 15/3/2017 
I would like to see:
$49.5 revenue for january

$99 revenue for february

$49.5 revenue for march

[KHorseman]

Yeah I get that example. I guess what I'm asking is what happens if they subscribe on February 21st or February 8th? Still 50% for both of them?

[Matjo]

Ah, sorry. They can sign on whenever they want but it will either be recorded as the beginning or the middle of the month. 
So it the start date in the table will be 1st or 15th and the end date can be 28th, 29th, 30th or 31st depending on the month. 

[KHorseman]

So then what's the rule exactly? If they sign up on or before the 15th it's 50%, and if they sign up any date after the 15th it's 100%?

[Matjo]

The rule is if they sign on day 1-15 their subscription will start on the 15th, if the sign on 15-31 their subscription will start the 1st. If they cancel day 1-15 the end date will be 15 otherwise the end of the month. 

[v-jiascu-msft · Accepted solution]

Hi @Matjo,

The time is interval in your data model. If you want to filter the dates, the dates in the interval will be missed. So a date table is necessary.

1. Create a date table.

Calendar =
CALENDARAUTO ()

2. Join the date table and your data table.

Table =
FILTER (
    CROSSJOIN ( 'Table1', FILTER ( 'Calendar', DAY ( [Date] ) = 1 ) ),
    [Date]
        >= [Start_Date] - 31
        && [Date] <= [End_Date]
)

3. Add a Calculated Column.

Fee = if([Date]=[Start_Date],[Monthly_Fee],
if([Start_Date].[Year]=[Date].[Year]&&[Start_Date].[Month]=[Date].[Month],[Monthly_Fee]/2,
if([End_Date]=EOMONTH([Date],0),[Monthly_Fee],
if([End_Date].[Year]=[Date].[Year]&&[End_Date].[Month]=[Date].[Month], [Monthly_Fee]/2,[Monthly_Fee]))))

4. Create a visual, you can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgS4V8tw_INyjXVE1

Best Regards!

Dale