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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

try

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Groupby.pngExpand Data.pngFinal Table.png

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

Groupby.pngExpand Data.pngFinal Table.png

Using this formula it will only return the first date of my calender table. If I want to see the first sales date of my table this formula will not work. I need calculate the min date of the calender table when sales values in not blank. Can anyone help me on this.

Anonymous
Not applicable

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!

Anonymous
Not applicable

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

And what if I have multiple types of binders (e.g. red, green) defined in an aditioanl column, and now we want to return all binders with first date (there might be 2).  How to write function to retain both Binder green and binder red record:

ITEMCOLOROrderDate 
BinderRed2018-01-10 
BinderGreen2018-01-10 
BinderRed2018-02-10 
BinderRed2018-03-10 
BinderRed2018-04-10 
Binder Red2018-05-10 
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

try

 

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

Regards
Zubair

Please try my custom visuals

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors