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

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