Forum Discussion
Calculated Column Dax based on multiple variables to get total subscription value
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.
hi Anonymous
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
2 Replies
Anonymous , 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.hi Anonymous
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
