cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

 

image.png

 

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!
 
Thanks in advance!

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

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

@rdcooper 

 

try

 

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

View solution in original post

Highlighted
Anonymous
Not applicable

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

Dear rdcooper,

 

Is this you expected?

 

Drilldown Groupby-1.PNG

 

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,

Pradeep

View solution in original post

Highlighted
Anonymous
Not applicable

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

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

Groupby.pngExpand Data.pngFinal Table.png

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

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

@rdcooper 

 

try

 

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

View solution in original post

Highlighted
Anonymous
Not applicable

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

Dear rdcooper,

 

Is this you expected?

 

Drilldown Groupby-1.PNG

 

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,

Pradeep

View solution in original post

Highlighted
Anonymous
Not applicable

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

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

Groupby.pngExpand Data.pngFinal Table.png

View solution in original post

Highlighted
Advocate I
Advocate I

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

Wow thank you so much @Zubair_Muhammad, @Anonymous, @Anonymous - 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!

Highlighted
Frequent Visitor

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

How to take this measure one step further by calculating when the Due Date = Origional Due Date, (Y/N or T/F)?  Where to incert an if statment or a variable?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors