Create a new table from existing table to break down an annual payment into monthly upcoming MRR

[gioris]

Hi

I have a table with actual payments for a SASS product.

there is a monthly subscription and a yearly subscription option.

The schema of the table is 
_Charge_id
_Team_id
_date
_amount
_plan (yearly or monthly)

I need to create an auxiliary table that calculates upcoming payments so I can create metrics that will compare expected payments versus actual payments and churned revenue.


so in the case of the first ever payment of a team_id I need to create a recurring row in a new table (let's name it upcoming_payments)
if the first ever payment of a team id is "monthly" create one row per month for N periods with the monthly value
if the first payment of a team is "yearly" create one row per month for 12 periods with 1/12 of the annual payment.

Maybe there is an another way around this that I haven't thought of using just measures and DAX
so any suggestion would be welcome

thanks in advance

[v-jiascu-msft]

Hi @gioris,

Can you share a dummy sample data and the expected result please?

1. Do you want to create a calculated table or a table visual?

2. Maybe you need a date table in such scenario.

3. How to calculate the forecast values? 

Best Regards,

Dale

[gioris]

@v-jiascu-msft
I need a calculated table so I can have measures etc,

I already have a date table in my model

Basic Table 

charge_id	Team ID	Date Can	Amount	Plan	Date	is first payment
3320	986	22/3/2017	5.529,00 €	business_year	22/3/2017 0:00	yes
4516	2033	12/7/2017	1.620,00 €	business_month	12/7/2017 0:00	yes

so only in the case of first payments
if business year then create for the next 12 months a row for each expected MRR payment with  1/12 of the annual payment, and an expected date (date +30 would do)

if business month then for N next months create a row with the date+30 days and the same amount/ expected MRR

Expected New Table

first_charge_id	Team ID	Date Can	MRR expected	Plan	Base Amount
3723	2802	6/6/2017	460,75 €	business_year	5.529,00 €
3723	2802	6/7/2017	460,75 €	business_year	5.529,00 €
3723	2802	5/8/2017	460,75 €	business_year	5.529,00 €
3723	2802	4/9/2017	460,75 €	business_year	5.529,00 €
3723	2802	4/10/2017	460,75 €	business_year	5.529,00 €
3723	2802	3/11/2017	460,75 €	business_year	5.529,00 €
3723	2802	3/12/2017	460,75 €	business_year	5.529,00 €
3723	2802	2/1/2018	460,75 €	business_year	5.529,00 €
3723	2802	1/2/2018	460,75 €	business_year	5.529,00 €
3723	2802	3/3/2018	460,75 €	business_year	5.529,00 €
3723	2802	2/4/2018	460,75 €	business_year	5.529,00 €
4516	2033	12/7/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	11/8/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	10/9/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	10/10/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	9/11/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	9/12/2017	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	8/1/2018	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	7/2/2018	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	9/3/2018	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	8/4/2018	1.620,00 €	bussiness_month	1.620,00 €
4516	2033	8/5/2018	1.620,00 €	bussiness_month	1.620,00 €

thanks