Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
jimmy46
Regular Visitor

Dax Formula not returning values

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.

 

jimmy46_0-1721251787553.png

 

What could be the cause of this issue? Thanks for the help!

 

3 REPLIES 3
jimmy46
Regular Visitor

Hi Yulia, here's some screenshots of the data:

jimmy46_0-1721321753713.png

jimmy46_1-1721321780094.png

 

It's some simple Short date data. 

 

 

Hi @jimmy46 

 

I would like to apologize for the belated reply.

 

Below is my test.

 

sample data:

vxuxinyimsft_5-1722587405890.png

 

vxuxinyimsft_3-1722586196804.png

 

relationship:

vxuxinyimsft_7-1722587574714.png

 

I used the formula you gave directly, and the result is as follows

vxuxinyimsft_4-1722586481336.png

 

vxuxinyimsft_6-1722587452705.png

 

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.

v-xuxinyi-msft
Community Support
Community Support

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.

vxuxinyimsft_0-1721280979495.png

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.