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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

a1.png

 

You may create two measures as follows.

 

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

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

 

 

Result:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

a1.png

 

You may create two measures as follows.

 

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

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

 

 

Result:

a2.png

 

Best Regards

Allan

 

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

amitchandak
Super User
Super User

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

 

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.