Calculated Column Dax based on multiple variables to get total subscription value

[BobKoenen]

Hi You all,

I am trying to create a column in DAX which gives me the total value of a subscription and I know what I want but not how to do it. I hope you can help me. My data looks like this:

Customer	Value	Start date	End Date	Billing interval	Desired column of total value
A	100	1-1-2020	31-11-2020	Month	1100
B	25	1-1-2020	31-12-2020	4 weekly	325
C	5000	1-6-2020	30-6-2020	Incidental	5000
D	1500	1-4-2020	31-12-2020	Quarterly	4500

Sow i need a formula that depending on the Billing interval

Month = Difference in months between start date and End date * value

4 week = (Difference in Weeks between start date and End date/4 ) * Value

Incidental = value

Quarterly = Difference in quarters between start date and End Date * Value

I hope you can help me. 

[v-lili6-msft · Accepted solution]

hi  @BobKoenen 

You could create a column as below:

Result = SWITCH('Table'[Billing interval ],
"Month",'Table'[Value]*(DATEDIFF('Table'[Start date],'Table'[End Date],MONTH)+1),
"4 weekly",'Table'[Value]*INT(DIVIDE((DATEDIFF('Table'[Start date],'Table'[End Date],DAY)+1),7*4)),
"Incidental",'Table'[Value],
"Quarterly",'Table'[Value]*(DATEDIFF('Table'[Start date],'Table'[End Date],QUARTER)+1))

Result:

And here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

@BobKoenen , Create a new column like.  Change logic as per need

new column =
var _diff = datediff([start date], [end date],day)
return
switch( true(),
[Billing interval] ="Incidental", [value],
[Billing interval] ="weekly", [value]*_diff/4,
[value]*_diff
)

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.