cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX Formula to Return First Date Based on Other column

Hi All,

I'm trying to create a column that calculates the original due date for a set of items that have due dates that can be extended over and over again. For example, in this table, I want the "Original Due Date" column to look up the earliest date from the "Due Date" column based on the "Item" name (i.e. for the "Blue" Item, the original due date was 1/5/18, and for the "Red" Item, the original due date was 3/2/19).

Here is the formula I am trying:

Original Due Date = CALCULATE(MIN('Table'[Due Date]),FILTER('Table','Table'[Item]))

The problem is it's throwing the error: "Cannot convert value 'Blue' of type Text to type True/False".

Do any of you DAX geniuses (genii) out there know where I'm going wrong? Any help is much appreciated!

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III

## Re: DAX Formula to Return First Date Based on Other column

try

```Original Due Date =
CALCULATE ( MIN ( 'Table'[Due Date] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) )
```
Try my new Power BI game Cross the River
Anonymous
Not applicable

## Re: DAX Formula to Return First Date Based on Other column

Dear rdcooper,

Is this you expected?

The DAX Expression i have used to get this as follows.

Measure_OrderDate = CALCULATE(MIN(PBI_Data[OrderDate]), ALLEXCEPT(PBI_Data,PBI_Data[Item]))

Regards,

Super User I

## Re: DAX Formula to Return First Date Based on Other column

You can use Groupby in PQ to get that figure pretty easily:

4 REPLIES 4
Super User III

## Re: DAX Formula to Return First Date Based on Other column

try

```Original Due Date =
CALCULATE ( MIN ( 'Table'[Due Date] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) )
```
Try my new Power BI game Cross the River
Anonymous
Not applicable

## Re: DAX Formula to Return First Date Based on Other column

Dear rdcooper,

Is this you expected?

The DAX Expression i have used to get this as follows.

Measure_OrderDate = CALCULATE(MIN(PBI_Data[OrderDate]), ALLEXCEPT(PBI_Data,PBI_Data[Item]))

Regards,

Super User I

## Re: DAX Formula to Return First Date Based on Other column

You can use Groupby in PQ to get that figure pretty easily:

Frequent Visitor

## Re: DAX Formula to Return First Date Based on Other column

Wow thank you so much @Zubair_Muhammad, @Anonymous, @Nick_M - can't believe it was that easy! I had actually found the similar solution @Zubair_Muhammad offered in an almost identical post to mine (here), but I didn't think "ALLEXCEPT" would make sense since the name of the function is counterintuitive - I would have thought that would have returned the earliest due date for all rows except the ones with the Item name I was interested in. Weirdly enough, it works though. Thanks again!

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors