Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    try

     

    Original Due Date =
CALCULATE ( MIN ( 'Table'[Due Date] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) )
  • Anonymous's avatar
    Anonymous
    7 years ago

    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,

    Pradeep

  • Anonymous's avatar
    Anonymous
    7 years ago

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

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    try

     

    Original Due Date =
CALCULATE ( MIN ( 'Table'[Due Date] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) )
    • Fro88er's avatar
      Fro88er
      Helper IV
      6 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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,

    Pradeep

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!

    • indradutta's avatar
      indradutta
      New Member
      3 years ago

      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.