cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Penn Regular Visitor
Regular Visitor

DAX Question: Return the first date based on another column

Hi Everyone,

 

My first post here, very new to Power BI. Let's imaging a table [Work Status] that looks like below.

 

Annotation 2019-03-05 134718.jpg

 

Now I want to create a new column to return the first date of Finished status for each unique Item ID which is 7/11/2013.

 

The DAX which I wrote doesn't work:

Status Finished Date = CALCULATE(MIN('Work Status'[Date From], FILTER('Work Status', 'Work Status'[Status] = "Finished"))

 

Proprobaly I should not use CALCULATE?

 

Need help thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX Question: Return the first date based on another column

@Penn 

 

Sorry I think I misplaced the brackets

 

Try this one

 

Status Finished Date =
CALCULATE (
    MIN ( 'Work Status'[Date From] ),
    FILTER (
        ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
        'Work Status'[Status] = "Finished"
    )
)
4 REPLIES 4
Super User
Super User

Re: DAX Question: Return the first date based on another column

@Penn 

 

Try with this revision

 

Status Finished Date =
CALCULATE (
    MIN (
        'Work Status'[Date From],
        FILTER (
            ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
            'Work Status'[Status] = "Finished"
        )
    )
)

 

Penn Regular Visitor
Regular Visitor

Re: DAX Question: Return the first date based on another column

Hi Zubair,

 

I tried your code in both measure and calculated column and it returns me the "single value cannot be determined" error.

 

How should I fix this?

 

 

Super User
Super User

Re: DAX Question: Return the first date based on another column

@Penn 

 

Sorry I think I misplaced the brackets

 

Try this one

 

Status Finished Date =
CALCULATE (
    MIN ( 'Work Status'[Date From] ),
    FILTER (
        ALLEXCEPT ( 'Work Status', 'Work Status'[item id] ),
        'Work Status'[Status] = "Finished"
    )
)
Penn Regular Visitor
Regular Visitor

Re: DAX Question: Return the first date based on another column

You are absolutely briliant!!! Thank you!!!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 120 members 1,644 guests
Please welcome our newest community members: