Calculate the total value of a subscription

[BobKoenen]

Hi all,

I am trying to calculate the total value of a subscription which gives me headaches ;). 

My data looks like this:

subscription	Startdate line	Einddate line	amount	interval
A	1-1-2020	31-6-2020	200	Month
A	1-7-2020	31-12-2020	400	Month
B	1-1-2020	31-3-2020	100	Quarter
B	1-4-2020	31-12-2020	500	Quarter
C	1-1-2020	31-12-2020	5000	Year
D	1-1-2020	31-12-2020	400	4 weekly

The output should give me

A = (6* 200) + 6*(400) = 3600

B = 100 + (3*500) = 1600

C = 5000
D = (52/4) * 400)

I hope you can help me

[Mariusz · Accepted solution]

Hi @BobKoenen 

You can create a column like.

Column = 
SWITCH(
    'Table'[interval],
    "Month", DATEDIFF( 'Table'[Startdate line], 'Table'[Einddate line], MONTH ) +1,
    "Quarter", DATEDIFF( 'Table'[Startdate line], 'Table'[Einddate line], QUARTER ) +1,
    "Year", DATEDIFF( 'Table'[Startdate line], 'Table'[Einddate line], MONTH ) +1, 
    "4 weekly", ( DATEDIFF( 'Table'[Startdate line], 'Table'[Einddate line], WEEK ) + 1 ) /4
) * 'Table'[amount]

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn