英文:
How to calculate Monthly Recurring Revenue using DAX?
问题
你的最终目标是拥有一个月度定期收入的订阅服务。你的规则是：
- 合同列必须与0不同。
- 以下表格已经与日历维度相关联，使用另一列。
你的目标是得到以下结果：
| 月份 | 收入 |
|---|---|
| 01/2023 | 1000 |
| 02/2023 | 1000 |
| 03/2023 | 2000 |
| 04/2023 | 3000 |
| 05/2023 | 4000 |
| 06/2023 | 3000 |
| 07/2023 | 3000 |
| 08/2023 | 2000 |
| 09/2023 | 1000 |
| 10/2023 | 0 |
然后将其显示在一条线图中。但你不知道如何计算这个。你尝试使用以下公式，但它不会按月份相加：
MRR = CALCULATE(SUM(CRMTable[Revenue]), CRMTable[Contract] <> "0")
英文:
My end goal is to have a Monthly Recurring Revenue for a subscription service. Example:
Rules:
- Contract column must be different than 0
- The following table is already related to the calendar dimension using another column.
CRMTable
| Contract | ContractDate | ContractEnd | Revenue |
|---|---|---|---|
| X | 01/01/2023 | 01/06/2023 | 1000 |
| 0 | 01/02/2023 | 01/07/2023 | 1000 |
| X | 01/03/2023 | 01/08/2023 | 1000 |
| X | 01/04/2023 | 01/09/2023 | 1000 |
| X | 01/05/2023 | 01/10/2023 | 1000 |
The goal is to have this result:
| Month | Revenue |
|---|---|
| 01/2023 | 1000 |
| 02/2023 | 1000 |
| 03/2023 | 2000 |
| 04/2023 | 3000 |
| 05/2023 | 4000 |
| 06/2023 | 3000 |
| 07/2023 | 3000 |
| 08/2023 | 2000 |
| 09/2023 | 1000 |
| 10/2023 | 0 |
| ... |
And then display it in a line graph. But I don't know how to calculate this. Already used chatGPT but it didn't help much.
Thanks in advance!
I tried using this but it doesn't add monthly.
MRR = CALCULATE(SUM(CRMTable[Revenue]), CRMTable[Contract] <> "0")
答案1
得分: 0
我基于一个不相关的单列表格开发了这个，该表格包括每个月的第一天，例如 2023 年 01 月 01 日，2023 年 01 月 02 日等。然后，您可以使用这些月份创建一个数据透视表，同时使用以下度量值：
=
VAR ThisDate =
MIN( Months[Month] )
RETURN
SUMX(
FILTER(
CRMTable,
CRMTable[Contract] <> "0"
&& CRMTable[ContractDate] <= EOMONTH( ThisDate, 0 )
&& CRMTable[ContractEnd] > ThisDate
),
CRMTable[Revenue]
)
英文:
I have developed this based on an unrelated, single-column table which comprises the first day of each month, e.g. 01/01/2023, 01/02/2023, etc. You can then create a Pivot Table using these months together with the following measure:
=
VAR ThisDate =
MIN( Months[Month] )
RETURN
SUMX(
FILTER(
CRMTable,
CRMTable[Contract] <> "0"
&& CRMTable[ContractDate] <= EOMONTH( ThisDate, 0 )
&& CRMTable[ContractEnd] > ThisDate
),
CRMTable[Revenue]
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论