Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello everyone, I'm still new to PowerBI and trying to get the hang of things.
I have two tables called Opportunity and Sales Rep Promotion dates connected by OwnerId to SFDC ID.
I created a formula column in the Opportunity table that calculates the first date for when each rep closed their first opportunity, which is working properly.
FirstCloseDate =
CALCULATE(
MIN('Opportunity'[CloseDate]),
ALLEXCEPT('Opportunity', 'Opportunity'[OwnerId])
)
Now I'm trying to calculate the difference between each rep's start date, and the "FirstCloseDate". This is the formula I'm using:
DaysToFirstDeal =
VAR FirstCloseDate =
CALCULATE(
MIN('Opportunity'[FirstCloseDate]),
FILTER(
'Opportunity',
'Opportunity'[OwnerId] = 'Sales rep start dates'[User ID]
)
)
RETURN
IF(
ISBLANK(FirstCloseDate),
BLANK(),
DATEDIFF('Sales rep start dates'[Start date], FirstCloseDate, DAY)
)
The formula doesn't return any errors. However, it's not showing any value and I can't figure out why.
What could be the cause of this issue? Thanks for the help!
Hi Yulia, here's some screenshots of the data:
It's some simple Short date data.
Hi @jimmy46
I would like to apologize for the belated reply.
Below is my test.
sample data:
relationship:
I used the formula you gave directly, and the result is as follows
I can't reproduce your problem, could you please provide more details?
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jimmy46
Could you please provide some sample data and the expected results based on the sample data? That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
At present, I can only give you some suggestions based on your formula for your reference.
Since your previous formula, which works fine, has already calculated the earliest closing opportunity time for each OwnerID, and from your description it seems that there is a relationship between the two tables, I think the second formula may not need to calculate FirstCloseDate again. You can try to use the first formula directly in the second formula.
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |