Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Maximum Difference in Date Values

I have 5 different date value columns (each signifying the last scan date for 5 categories of devices). I want to create a column that would take the oldest date from these 5 columns and calculate the difference in days from TODAY. 

 

How should I do this?

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I also suggest you use one date column. I created data t reproduce your scenario.

    Table:

     

    You may create two measures as follows.

     

    The oldest date = 
CALCULATE(
    MIN('Table'[Date]),
    ALL('Table')
)

Difference = 
DATEDIFF(
    [The oldest date],
    NOW(),
    DAY
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Instead of maintaining 5 column, we should have all dates in 1 column.  Share some data and show the expected result.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I think the best way is, what suggested by Ashish_Mathur .

    You can try

     

    min date =
var _tab = union(all(table[Date1]),all(table[Date2]),all(table[Date3]),all(table[Date4]),all(table[Date5]))
Return
minx(_tab,[date1])

     

    I have not tried it so Just check name suggest for date by pressing [  in last line

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I also suggest you use one date column. I created data t reproduce your scenario.

    Table:

     

    You may create two measures as follows.

     

    The oldest date = 
CALCULATE(
    MIN('Table'[Date]),
    ALL('Table')
)

Difference = 
DATEDIFF(
    [The oldest date],
    NOW(),
    DAY
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.