英文:
Calculate date difference between two specific categories
问题
我想计算在“PROSPECT”阶段开始并在“GAIN”阶段结束的客户的入场日期和“GAIN”阶段开始日期之间的差异，也就是他们成为我们客户所需的时间。谢谢您提前的帮助。
英文:
Well, this problem is hard to explain, but here we go.
Consider this a tab with the following columns:
ID,
TITLE,
PHASE,
START_DATE, ( id enters the phase)
END_DATE, (id leaves the phase)
I want to calculate the difference between start and end date, this would be quite simple but...
I need to calculate the difference between the START_DATE of the phase "PROSPECT" and the START_DATE of "GAIN" phase.
And it needs to be calculated ONLY for the ID's that started in the "PROSPECT" phase until "GAIN" phase.
Any other ID needs to be kept out of this measure.
Explaining what I want:
The ID's are clients, I want to calculate how long it takes for our clients who enters as a prospect to reach the gain phase, which means, become our client.
Thank-you in advance.
I did the same question in PBI Community: https://community.powerbi.com/t5/Desktop/Calculate-date-difference-between-two-specific-categories/m-p/3068863#M1041877
答案1
得分: 0
你需要类似这样的内容，其中你计算感兴趣的两个阶段的开始日期，但保留了对选定的“ID”的筛选条件：
Diff =
VAR _prospect =
CALCULATE (
MAX ( 'Table'[START_DATE] ) ,
ALLEXCEPT (
'Table' ,
'Table'[ID]
) ,
'Table'[PHASE] = "PROSPECT"
)
VAR _gain =
CALCULATE (
MAX ( 'Table'[START_DATE] ) ,
ALLEXCEPT (
'Table' ,
'Table'[ID]
) ,
'Table'[PHASE] = "GAIN"
)
RETURN
IF (
HASONEVALUE ( 'Table'[ID] )
&& _prospect <> BLANK ()
&& _gain <> BLANK () ,
DATEDIFF ( _prospect , _gain , DAY )
)
在这里，我还删除了对于给定ID，要么_prospect要么_gain计算结果为空的情况。如果存在多个ID，则还进行了短路计算。
如果你想要计算ID的平均值，你可以使用类似这样的度量，使用相同的度量在迭代器中：
Avg Diff =
AVERAGEX (
VALUES ( 'Table'[ID] ) ,
[Diff]
)
英文:
You need something like this, where you calculate the start dates for the two phases of interest, but retain a filter on the selected ID:
Diff =
VAR _prospect =
CALCULATE (
MAX ( 'Table'[START_DATE] ) ,
ALLEXCEPT (
'Table' ,
'Table'[ID]
) ,
'Table'[PHASE] = "PROSPECT"
)
VAR _gain =
CALCULATE (
MAX ( 'Table'[START_DATE] ) ,
ALLEXCEPT (
'Table' ,
'Table'[ID]
) ,
'Table'[PHASE] = "GAIN"
)
RETURN
IF (
HASONEVALUE ( 'Table'[ID] )
&& _prospect <> BLANK ()
&& _gain <> BLANK () ,
DATEDIFF ( _prospect , _gain , DAY )
)
Here I also remove any cases where either _prospect or _gain are evaluated to be blank for the given ID. I also short-circuit the calculation if there are more than 1 IDs present.
If you want the average for your IDs then you can look at a measure like this, using the same measure in an iterator:
Avg Diff =
AVERAGEX (
VALUES ( 'Table'[ID] ) ,
[Diff]
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论