Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Help - Date Comparison between 2 Tables

I've been stuggling with the Dax that will compair all dates per "ID" and provide the max date ("Expected Results"). Can someone provide assistance on the best approach

 

Table 1 Table 2  
IDDG6N3500R3450 IDClose Date Expected Result
110/15/2021   110/26/2021 10/26/2021
2 11/1/2021  2  11/1/2021
3 1/10/202212/10/2021 3  1/10/2022
4 1/29/20211/22/2022 43/15/2022 3/15/2022

Thanks,

Debbie

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ，

     

    Please create a new measure.

    Expected Result = 
var _id1 = MAX('Table1'[ID])
var _id2 = MAX('Table2'[ID])
var _value_1 = CALCULATE(MAX('Table1'[DG6]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_2 = CALCULATE(MAX('Table1'[R3450]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_3 = CALCULATE(MAX('Table1'[N3500]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_4 = CALCULATE(MAX('Table2'[Close Date]),FILTER(ALL(Table2),'Table2'[ID]=_id2))
return
MAX(_value_4,MAX(_value_3,MAX(_value_2,_value_1)))

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hopefully this is more helpful. The logic I'm looking for is take the CLOSE_DATE else take the Max date.

       

      Thanks,

      Debbie

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        And I'm using DirectQuery

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ，

     

    Please create a new measure.

    Expected Result = 
var _id1 = MAX('Table1'[ID])
var _id2 = MAX('Table2'[ID])
var _value_1 = CALCULATE(MAX('Table1'[DG6]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_2 = CALCULATE(MAX('Table1'[R3450]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_3 = CALCULATE(MAX('Table1'[N3500]),FILTER(ALL(Table1),'Table1'[ID]=_id1))
var _value_4 = CALCULATE(MAX('Table2'[Close Date]),FILTER(ALL(Table2),'Table2'[ID]=_id2))
return
MAX(_value_4,MAX(_value_3,MAX(_value_2,_value_1)))

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data