Periodic billing cycles with quarters

[LaraLiselotte]

Hi all,

I am trying to create an overview the expected revenue per month for a subscription based product which our customers can either pay per month, per year or per quarter.

I found a couple of examples that have been helpful such as the periodic billing example by Greg_Deckler, however this examples doesn’t take into account different pay frequencies.
I am mainly struggling to calculate the quarter revenue on the right dates.

My data looks like this:

customer	Starting date	frequency	value	total value
1	1-1-2021	year	100	100
2	1-2-2021	month	10	120
3	1-3-2021	quarter	25	100
4	1-4-2021	quarter	100	100
5	1-5-2021	month	10	120

So for example for customer 3, their subscription starts on 1-3-2021 and their next payment should be on 1-6-2021 and the next on 1-9-2021 etc. 

My expected outcome would be something like this:

[amitchandak · Accepted solution]

@LaraLiselotte , Try a new table like

Addcolumns(
filter( generate(Table, generate(1,24,1) ), ( [frequency] ="Year" && [Value] <=2 ) ||

( [frequency] ="quarter" && [Value] <=8 ) || ( [frequency] ="month" && [Value] <=24 ) )
, "New Date" , date(year([Starting date]) +if([frequency] ="Year", [Value],0) , month([Starting date]) +if([frequency] ="quarter", [Value]*3,[Value]), day([Starting date]) ))

[LaraLiselotte]

Thank you for your reply!